One of my IT guys "disabled" the "BUILTIN\Administrator" account in SQL
server and now we can't connect to the server using Enterprise Manager.
None of the other logins have administration privileges.
How can we either re-enable the account or get administrative access to
SQL server?
--DaveIf you are running in mixed security mode you can use sa.
Christian Smith
"Dave Navarro" <dave@.dave.dave> wrote in message
news:MPG.1a96f8a675e3bc62989785@.news-40.giganews.com...
> One of my IT guys "disabled" the "BUILTIN\Administrator" account in SQL
> server and now we can't connect to the server using Enterprise Manager.
> None of the other logins have administration privileges.
> How can we either re-enable the account or get administrative access to
> SQL server?
> --Dave|||In article <elZTG3m8DHA.2752@.TK2MSFTNGP09.phx.gbl>, csmith@.digex.com
says...
> If you are running in mixed security mode you can use sa.
Tried it and it didn't work.
I had already installed Service Pack 3, but I don't think it was 3a.
The knowledge base seems to indicate that re/installing the service pack
will enable/create the BUILTIN/Administrator account.
Is that correct?
--Dave|||Also, is it the BUILTIN\Administrator user or the BUILTIN\Administrators
group? If it is the user than the group should still be there unless
removed. What about the user that SQL is running under? Was it explicitly
given access or was it using the BUILTIN access?
"Dave Navarro" <dave@.dave.dave> wrote in message
news:MPG.1a96f8a675e3bc62989785@.news-40.giganews.com...
> One of my IT guys "disabled" the "BUILTIN\Administrator" account in SQL
> server and now we can't connect to the server using Enterprise Manager.
> None of the other logins have administration privileges.
> How can we either re-enable the account or get administrative access to
> SQL server?
> --Dave|||SP3 and SP3a are pretty much the same. 3a is only there if you couldn't get
3 to install. Which article said you the SP would fix this? Do you have a
link? Also, how did sa not work? What did you try to do? The
BUILTIN\Adminsitrators is completely different from SQL security model that
sa uses. If you have the password you should be able to access the system
using SA.
Christian Smith
"Dave Navarro" <dave@.dave.dave> wrote in message
news:MPG.1a96fd2a91bf5623989786@.news-40.giganews.com...
> In article <elZTG3m8DHA.2752@.TK2MSFTNGP09.phx.gbl>, csmith@.digex.com
> says...
> > If you are running in mixed security mode you can use sa.
> Tried it and it didn't work.
> I had already installed Service Pack 3, but I don't think it was 3a.
> The knowledge base seems to indicate that re/installing the service pack
> will enable/create the BUILTIN/Administrator account.
> Is that correct?
> --Dave|||http://support.microsoft.com/search/default.aspx?
InCC_hdn=true&Catalog=LCID%3D1033%26CDID%3DEN-US-KB%26PRODLISTSRC%
3DON&withinResults=&QuerySource=gASr_Query&Product=sql2k&Queryc=builtin%
5Cadministrator&Query=builtin%
5Cadministrator&KeywordType=ALL&maxResults=150
&Titles=false&numDays=&InCC=on
I looked over most of the articles in this query and almost every single
one of them says to install/re-install the service pack to fix security
problems.
My train of thought was to try it and see what happens...
--
I was able to get in using info from the following article:
http://www.microsoft.com/sql/techinfo/tips/administration/May3.asp
We're back up and running now.
Thanks for taking the time to help, it's deeply appreciated.
--Dave
In article <eG76HDn8DHA.3404@.TK2MSFTNGP09.phx.gbl>, csmith@.digex.com
says...
> SP3 and SP3a are pretty much the same. 3a is only there if you couldn't get
> 3 to install. Which article said you the SP would fix this? Do you have a
> link? Also, how did sa not work? What did you try to do? The
> BUILTIN\Adminsitrators is completely different from SQL security model that
> sa uses. If you have the password you should be able to access the system
> using SA.
> Christian Smith
> "Dave Navarro" <dave@.dave.dave> wrote in message
> news:MPG.1a96fd2a91bf5623989786@.news-40.giganews.com...
> > In article <elZTG3m8DHA.2752@.TK2MSFTNGP09.phx.gbl>, csmith@.digex.com
> > says...
> > > If you are running in mixed security mode you can use sa.
> >
> > Tried it and it didn't work.
> >
> > I had already installed Service Pack 3, but I don't think it was 3a.
> > The knowledge base seems to indicate that re/installing the service pack
> > will enable/create the BUILTIN/Administrator account.
> >
> > Is that correct?
Showing posts with label enterprise. Show all posts
Showing posts with label enterprise. Show all posts
Monday, March 26, 2012
OH NO! Please help!
One of my IT guys "disabled" the "BUILTIN\Administrator" account in SQL
server and now we can't connect to the server using Enterprise Manager.
None of the other logins have administration privileges.
How can we either re-enable the account or get administrative access to
SQL server?
--DaveIf you are running in mixed security mode you can use sa.
Christian Smith
"Dave Navarro" <dave@.dave.dave> wrote in message
news:MPG.1a96f8a675e3bc62989785@.news-40.giganews.com...
> One of my IT guys "disabled" the "BUILTIN\Administrator" account in SQL
> server and now we can't connect to the server using Enterprise Manager.
> None of the other logins have administration privileges.
> How can we either re-enable the account or get administrative access to
> SQL server?
> --Dave|||In article <elZTG3m8DHA.2752@.TK2MSFTNGP09.phx.gbl>, csmith@.digex.com
says...
> If you are running in mixed security mode you can use sa.
Tried it and it didn't work.
I had already installed Service Pack 3, but I don't think it was 3a.
The knowledge base seems to indicate that re/installing the service pack
will enable/create the BUILTIN/Administrator account.
Is that correct?
--Dave|||Also, is it the BUILTIN\Administrator user or the BUILTIN\Administrators
group? If it is the user than the group should still be there unless
removed. What about the user that SQL is running under? Was it explicitly
given access or was it using the BUILTIN access?
"Dave Navarro" <dave@.dave.dave> wrote in message
news:MPG.1a96f8a675e3bc62989785@.news-40.giganews.com...
> One of my IT guys "disabled" the "BUILTIN\Administrator" account in SQL
> server and now we can't connect to the server using Enterprise Manager.
> None of the other logins have administration privileges.
> How can we either re-enable the account or get administrative access to
> SQL server?
> --Dave|||SP3 and SP3a are pretty much the same. 3a is only there if you couldn't get
3 to install. Which article said you the SP would fix this? Do you have a
link? Also, how did sa not work? What did you try to do? The
BUILTIN\Adminsitrators is completely different from SQL security model that
sa uses. If you have the password you should be able to access the system
using SA.
Christian Smith
"Dave Navarro" <dave@.dave.dave> wrote in message
news:MPG.1a96fd2a91bf5623989786@.news-40.giganews.com...
> In article <elZTG3m8DHA.2752@.TK2MSFTNGP09.phx.gbl>, csmith@.digex.com
> says...
> Tried it and it didn't work.
> I had already installed Service Pack 3, but I don't think it was 3a.
> The knowledge base seems to indicate that re/installing the service pack
> will enable/create the BUILTIN/Administrator account.
> Is that correct?
> --Dave|||http://support.microsoft.com/search/default.aspx?
InCC_hdn=true&Catalog=LCID%3D1033%26CDID%3DEN-US-KB%26PRODLISTSRC%
3DON&withinResults=&QuerySource=gASr_Query&Product=sql2k&Queryc=builtin%
5Cadministrator&Query=builtin%
5Cadministrator&KeywordType=ALL&maxResults=150
&Titles=false&numDays=&InCC=on
I looked over most of the articles in this query and almost every single
one of them says to install/re-install the service pack to fix security
problems.
My train of thought was to try it and see what happens...
I was able to get in using info from the following article:
http://www.microsoft.com/sql/techin...ration/May3.asp
We're back up and running now.
Thanks for taking the time to help, it's deeply appreciated.
--Dave
In article <eG76HDn8DHA.3404@.TK2MSFTNGP09.phx.gbl>, csmith@.digex.com
says...
> SP3 and SP3a are pretty much the same. 3a is only there if you couldn't g
et
> 3 to install. Which article said you the SP would fix this? Do you have
a
> link? Also, how did sa not work? What did you try to do? The
> BUILTIN\Adminsitrators is completely different from SQL security model tha
t
> sa uses. If you have the password you should be able to access the system
> using SA.
> Christian Smith
> "Dave Navarro" <dave@.dave.dave> wrote in message
> news:MPG.1a96fd2a91bf5623989786@.news-40.giganews.com...
server and now we can't connect to the server using Enterprise Manager.
None of the other logins have administration privileges.
How can we either re-enable the account or get administrative access to
SQL server?
--DaveIf you are running in mixed security mode you can use sa.
Christian Smith
"Dave Navarro" <dave@.dave.dave> wrote in message
news:MPG.1a96f8a675e3bc62989785@.news-40.giganews.com...
> One of my IT guys "disabled" the "BUILTIN\Administrator" account in SQL
> server and now we can't connect to the server using Enterprise Manager.
> None of the other logins have administration privileges.
> How can we either re-enable the account or get administrative access to
> SQL server?
> --Dave|||In article <elZTG3m8DHA.2752@.TK2MSFTNGP09.phx.gbl>, csmith@.digex.com
says...
> If you are running in mixed security mode you can use sa.
Tried it and it didn't work.
I had already installed Service Pack 3, but I don't think it was 3a.
The knowledge base seems to indicate that re/installing the service pack
will enable/create the BUILTIN/Administrator account.
Is that correct?
--Dave|||Also, is it the BUILTIN\Administrator user or the BUILTIN\Administrators
group? If it is the user than the group should still be there unless
removed. What about the user that SQL is running under? Was it explicitly
given access or was it using the BUILTIN access?
"Dave Navarro" <dave@.dave.dave> wrote in message
news:MPG.1a96f8a675e3bc62989785@.news-40.giganews.com...
> One of my IT guys "disabled" the "BUILTIN\Administrator" account in SQL
> server and now we can't connect to the server using Enterprise Manager.
> None of the other logins have administration privileges.
> How can we either re-enable the account or get administrative access to
> SQL server?
> --Dave|||SP3 and SP3a are pretty much the same. 3a is only there if you couldn't get
3 to install. Which article said you the SP would fix this? Do you have a
link? Also, how did sa not work? What did you try to do? The
BUILTIN\Adminsitrators is completely different from SQL security model that
sa uses. If you have the password you should be able to access the system
using SA.
Christian Smith
"Dave Navarro" <dave@.dave.dave> wrote in message
news:MPG.1a96fd2a91bf5623989786@.news-40.giganews.com...
> In article <elZTG3m8DHA.2752@.TK2MSFTNGP09.phx.gbl>, csmith@.digex.com
> says...
> Tried it and it didn't work.
> I had already installed Service Pack 3, but I don't think it was 3a.
> The knowledge base seems to indicate that re/installing the service pack
> will enable/create the BUILTIN/Administrator account.
> Is that correct?
> --Dave|||http://support.microsoft.com/search/default.aspx?
InCC_hdn=true&Catalog=LCID%3D1033%26CDID%3DEN-US-KB%26PRODLISTSRC%
3DON&withinResults=&QuerySource=gASr_Query&Product=sql2k&Queryc=builtin%
5Cadministrator&Query=builtin%
5Cadministrator&KeywordType=ALL&maxResults=150
&Titles=false&numDays=&InCC=on
I looked over most of the articles in this query and almost every single
one of them says to install/re-install the service pack to fix security
problems.
My train of thought was to try it and see what happens...
I was able to get in using info from the following article:
http://www.microsoft.com/sql/techin...ration/May3.asp
We're back up and running now.
Thanks for taking the time to help, it's deeply appreciated.
--Dave
In article <eG76HDn8DHA.3404@.TK2MSFTNGP09.phx.gbl>, csmith@.digex.com
says...
> SP3 and SP3a are pretty much the same. 3a is only there if you couldn't g
et
> 3 to install. Which article said you the SP would fix this? Do you have
a
> link? Also, how did sa not work? What did you try to do? The
> BUILTIN\Adminsitrators is completely different from SQL security model tha
t
> sa uses. If you have the password you should be able to access the system
> using SA.
> Christian Smith
> "Dave Navarro" <dave@.dave.dave> wrote in message
> news:MPG.1a96fd2a91bf5623989786@.news-40.giganews.com...
Friday, March 23, 2012
of the 8 versions of sql server 2000...
which one would I use to get used to the general interface of sql server programming on? -Standard and enterprise seem a little over the top for this purpose,
-it sounds like developer is for built for engineering 3rd party apps
-personal is geared toward roadwarriors...maybe it will work?
The programming interface is basically the same on all versions. Developer
Edition is a full-featured edition, targeted at people who build ANY apps
for SQL Server not just ISVs if that's what you mean by 3rd party apps.
Regards,
Greg Linwood
SQL Server MVP
"supernaut" <supernaut@.discussions.microsoft.com> wrote in message
news:90E5ABA4-BB05-4AB7-9CAA-85E92870B791@.microsoft.com...
> which one would I use to get used to the general interface of sql server
programming on? -Standard and enterprise seem a little over the top for
this purpose,
> -it sounds like developer is for built for engineering 3rd party apps
> -personal is geared toward roadwarriors...maybe it will work?
>
|||Beauty! Thanks for the info!
"Greg Linwood" wrote:
> The programming interface is basically the same on all versions. Developer
> Edition is a full-featured edition, targeted at people who build ANY apps
> for SQL Server not just ISVs if that's what you mean by 3rd party apps.
> Regards,
> Greg Linwood
> SQL Server MVP
> "supernaut·" <supernaut@.discussions.microsoft.com> wrote in message
> news:90E5ABA4-BB05-4AB7-9CAA-85E92870B791@.microsoft.com...
> programming on? -Standard and enterprise seem a little over the top for
> this purpose,
>
>
-it sounds like developer is for built for engineering 3rd party apps
-personal is geared toward roadwarriors...maybe it will work?
The programming interface is basically the same on all versions. Developer
Edition is a full-featured edition, targeted at people who build ANY apps
for SQL Server not just ISVs if that's what you mean by 3rd party apps.
Regards,
Greg Linwood
SQL Server MVP
"supernaut" <supernaut@.discussions.microsoft.com> wrote in message
news:90E5ABA4-BB05-4AB7-9CAA-85E92870B791@.microsoft.com...
> which one would I use to get used to the general interface of sql server
programming on? -Standard and enterprise seem a little over the top for
this purpose,
> -it sounds like developer is for built for engineering 3rd party apps
> -personal is geared toward roadwarriors...maybe it will work?
>
|||Beauty! Thanks for the info!
"Greg Linwood" wrote:
> The programming interface is basically the same on all versions. Developer
> Edition is a full-featured edition, targeted at people who build ANY apps
> for SQL Server not just ISVs if that's what you mean by 3rd party apps.
> Regards,
> Greg Linwood
> SQL Server MVP
> "supernaut·" <supernaut@.discussions.microsoft.com> wrote in message
> news:90E5ABA4-BB05-4AB7-9CAA-85E92870B791@.microsoft.com...
> programming on? -Standard and enterprise seem a little over the top for
> this purpose,
>
>
of the 8 versions of sql server 2000...
which one would I use to get used to the general interface of sql server pro
gramming on? -Standard and enterprise seem a little over the top for this p
urpose,
-it sounds like developer is for built for engineering 3rd party apps
-personal is geared toward roadwarriors...maybe it will work?The programming interface is basically the same on all versions. Developer
Edition is a full-featured edition, targeted at people who build ANY apps
for SQL Server not just ISVs if that's what you mean by 3rd party apps.
Regards,
Greg Linwood
SQL Server MVP
"supernaut" <supernaut@.discussions.microsoft.com> wrote in message
news:90E5ABA4-BB05-4AB7-9CAA-85E92870B791@.microsoft.com...
> which one would I use to get used to the general interface of sql server
programming on? -Standard and enterprise seem a little over the top for
this purpose,
> -it sounds like developer is for built for engineering 3rd party apps
> -personal is geared toward roadwarriors...maybe it will work?
>|||Beauty! Thanks for the info!
"Greg Linwood" wrote:
> The programming interface is basically the same on all versions. Developer
> Edition is a full-featured edition, targeted at people who build ANY apps
> for SQL Server not just ISVs if that's what you mean by 3rd party apps.
> Regards,
> Greg Linwood
> SQL Server MVP
> "supernaut·" <supernaut@.discussions.microsoft.com> wrote in message
> news:90E5ABA4-BB05-4AB7-9CAA-85E92870B791@.microsoft.com...
> programming on? -Standard and enterprise seem a little over the top for
> this purpose,
>
>sql
gramming on? -Standard and enterprise seem a little over the top for this p
urpose,
-it sounds like developer is for built for engineering 3rd party apps
-personal is geared toward roadwarriors...maybe it will work?The programming interface is basically the same on all versions. Developer
Edition is a full-featured edition, targeted at people who build ANY apps
for SQL Server not just ISVs if that's what you mean by 3rd party apps.
Regards,
Greg Linwood
SQL Server MVP
"supernaut" <supernaut@.discussions.microsoft.com> wrote in message
news:90E5ABA4-BB05-4AB7-9CAA-85E92870B791@.microsoft.com...
> which one would I use to get used to the general interface of sql server
programming on? -Standard and enterprise seem a little over the top for
this purpose,
> -it sounds like developer is for built for engineering 3rd party apps
> -personal is geared toward roadwarriors...maybe it will work?
>|||Beauty! Thanks for the info!
"Greg Linwood" wrote:
> The programming interface is basically the same on all versions. Developer
> Edition is a full-featured edition, targeted at people who build ANY apps
> for SQL Server not just ISVs if that's what you mean by 3rd party apps.
> Regards,
> Greg Linwood
> SQL Server MVP
> "supernaut·" <supernaut@.discussions.microsoft.com> wrote in message
> news:90E5ABA4-BB05-4AB7-9CAA-85E92870B791@.microsoft.com...
> programming on? -Standard and enterprise seem a little over the top for
> this purpose,
>
>sql
Friday, March 9, 2012
ODBC to MSDE
I need to write a VB.Net application in VS.net 2003 that copies several tables from an ODBC data source to MSDE tables. I do not have Enterprise Manager or DTS to use. Is there an example of this in VS? I was hoping VS has some built in tools for this. If not, bulk Insert?
Help appreciated.
thanks,
FrankI would consider using MS Access for this, as it accesses both ODBC and MSDE data sources quite well (presuming this is not something that needs to run like a service).|||I ruled out Access because of several reasons. My next step is to schedule the scripts thru the SQL agent that MSDE comes with.
Help appreciated.
Thanks,
Frank
ODBC timeout in sql server enterprise
Any advice on how to turn off the odbc timeout on a sql enterprise db query?Sean,
There are two setting for ODBC that you can use: SQL_QUERY_TIMEOUT and
SQL_LOGIN_TIMEOUT. The first is probably what you are after. Using this
for an ODBC connection is not sensitive to the edition of SQL Server
(Standard, Enterprise, etc) that you are using.
RLF
"Sean" <Sean@.discussions.microsoft.com> wrote in message
news:73E847C1-7FBE-4103-8EC1-E6E3FF3E6658@.microsoft.com...
> Any advice on how to turn off the odbc timeout on a sql enterprise db
> query?
There are two setting for ODBC that you can use: SQL_QUERY_TIMEOUT and
SQL_LOGIN_TIMEOUT. The first is probably what you are after. Using this
for an ODBC connection is not sensitive to the edition of SQL Server
(Standard, Enterprise, etc) that you are using.
RLF
"Sean" <Sean@.discussions.microsoft.com> wrote in message
news:73E847C1-7FBE-4103-8EC1-E6E3FF3E6658@.microsoft.com...
> Any advice on how to turn off the odbc timeout on a sql enterprise db
> query?
ODBC timeout in sql server enterprise
Any advice on how to turn off the odbc timeout on a sql enterprise db query?
Sean,
There are two setting for ODBC that you can use: SQL_QUERY_TIMEOUT and
SQL_LOGIN_TIMEOUT. The first is probably what you are after. Using this
for an ODBC connection is not sensitive to the edition of SQL Server
(Standard, Enterprise, etc) that you are using.
RLF
"Sean" <Sean@.discussions.microsoft.com> wrote in message
news:73E847C1-7FBE-4103-8EC1-E6E3FF3E6658@.microsoft.com...
> Any advice on how to turn off the odbc timeout on a sql enterprise db
> query?
Sean,
There are two setting for ODBC that you can use: SQL_QUERY_TIMEOUT and
SQL_LOGIN_TIMEOUT. The first is probably what you are after. Using this
for an ODBC connection is not sensitive to the edition of SQL Server
(Standard, Enterprise, etc) that you are using.
RLF
"Sean" <Sean@.discussions.microsoft.com> wrote in message
news:73E847C1-7FBE-4103-8EC1-E6E3FF3E6658@.microsoft.com...
> Any advice on how to turn off the odbc timeout on a sql enterprise db
> query?
ODBC timeout in sql server enterprise
Any advice on how to turn off the odbc timeout on a sql enterprise db query?Sean,
There are two setting for ODBC that you can use: SQL_QUERY_TIMEOUT and
SQL_LOGIN_TIMEOUT. The first is probably what you are after. Using this
for an ODBC connection is not sensitive to the edition of SQL Server
(Standard, Enterprise, etc) that you are using.
RLF
"Sean" <Sean@.discussions.microsoft.com> wrote in message
news:73E847C1-7FBE-4103-8EC1-E6E3FF3E6658@.microsoft.com...
> Any advice on how to turn off the odbc timeout on a sql enterprise db
> query?
There are two setting for ODBC that you can use: SQL_QUERY_TIMEOUT and
SQL_LOGIN_TIMEOUT. The first is probably what you are after. Using this
for an ODBC connection is not sensitive to the edition of SQL Server
(Standard, Enterprise, etc) that you are using.
RLF
"Sean" <Sean@.discussions.microsoft.com> wrote in message
news:73E847C1-7FBE-4103-8EC1-E6E3FF3E6658@.microsoft.com...
> Any advice on how to turn off the odbc timeout on a sql enterprise db
> query?
Saturday, February 25, 2012
ODBC problem - see out, but cant see in
Windows 2000 Advanced Server (latest service pack)
SQL Server 2000 Enterprise Edition (8.00.2187)
1. Server can see other machines in network through Windows Explorer
2. Server can connect to other machines in network through ODBC
3. Other machine can't connect to my server through ODBC
4. Other machine can browse shared folders on server through Windows Explorer
5. Worked fine yesterday, ODBC went out sometime before midnight last night
6. No events pointing to problems in the event log.
7. Problem is fixed with reboot.
8. Automatic updates is turned off
9. All updates were downloaded and installed about 1 1/12 weeks ago.
10. Server can be seen through Enterprise manager on server
11. Query analyzer cannot connect from remote machine
12. Query analyzer can connect if ran from server
What should I be looking for to make sure that this doesn't happen again? (I know that there has been more updates since then)You don't have DHCP turned on on the server, do you?|||Just client not server. This machine isn't a domain controller. It logs into another domain and isn't part of a cluster.|||On point 7, which machine (client or server) is rebooted to fix the problem? Also, can you check on the server using the Server Network Utility tool to see what protocols are supported there, and in what order? Do the same on the client using the Client Network Utility (cliconfg.exe, if it does not show up in Programs). Also check for aliases on the client (same utility, different tab).|||Server rebooted to fix problem.
Server - Only protocol enabled is TCP/IP.
Server - No aliases.
Client - TCP/IP & Named Pipes
Client - No aliases.|||Next time you get the error, check the exact wording of the message. If possible post it. I am out of ideas for the moment.
There are no firewalls, or other filtering devices between the two machines, right?|||It worked fine till last night. I haven't applied any hotfixes to SQL server in months.
The error was the same one that you get when you type the name of the server wrong or put in the wrong password - 'Server does not exist or access denied'.
I can't verify it, but I think some network 'tweaking' has been happening in the area. I can just blame it on the network guys, but I need to be sure that there's nothing wrong with my server.|||I can't find anything wrong with my machine. Everywhere I looked pointed to a problem with the firewall or a network problem. Since the reboot fixed everything and I haven't seen a problem since I am going to assume that there was some firewall changes or something like that.
Thanks for all the help everyone gave. Hopefully this problem will not come back.|||Did you try creating an Alias?
Could be a DNS thing
SQL Server 2000 Enterprise Edition (8.00.2187)
1. Server can see other machines in network through Windows Explorer
2. Server can connect to other machines in network through ODBC
3. Other machine can't connect to my server through ODBC
4. Other machine can browse shared folders on server through Windows Explorer
5. Worked fine yesterday, ODBC went out sometime before midnight last night
6. No events pointing to problems in the event log.
7. Problem is fixed with reboot.
8. Automatic updates is turned off
9. All updates were downloaded and installed about 1 1/12 weeks ago.
10. Server can be seen through Enterprise manager on server
11. Query analyzer cannot connect from remote machine
12. Query analyzer can connect if ran from server
What should I be looking for to make sure that this doesn't happen again? (I know that there has been more updates since then)You don't have DHCP turned on on the server, do you?|||Just client not server. This machine isn't a domain controller. It logs into another domain and isn't part of a cluster.|||On point 7, which machine (client or server) is rebooted to fix the problem? Also, can you check on the server using the Server Network Utility tool to see what protocols are supported there, and in what order? Do the same on the client using the Client Network Utility (cliconfg.exe, if it does not show up in Programs). Also check for aliases on the client (same utility, different tab).|||Server rebooted to fix problem.
Server - Only protocol enabled is TCP/IP.
Server - No aliases.
Client - TCP/IP & Named Pipes
Client - No aliases.|||Next time you get the error, check the exact wording of the message. If possible post it. I am out of ideas for the moment.
There are no firewalls, or other filtering devices between the two machines, right?|||It worked fine till last night. I haven't applied any hotfixes to SQL server in months.
The error was the same one that you get when you type the name of the server wrong or put in the wrong password - 'Server does not exist or access denied'.
I can't verify it, but I think some network 'tweaking' has been happening in the area. I can just blame it on the network guys, but I need to be sure that there's nothing wrong with my server.|||I can't find anything wrong with my machine. Everywhere I looked pointed to a problem with the firewall or a network problem. Since the reboot fixed everything and I haven't seen a problem since I am going to assume that there was some firewall changes or something like that.
Thanks for all the help everyone gave. Hopefully this problem will not come back.|||Did you try creating an Alias?
Could be a DNS thing
ODBC on 64 bit Enterprise SQL Server 2005
We are preparing to migrate all our 2000 databases to SQL Server 2005
and have run into an unexpected issue. We pull in a lot of Legacy Data
from a Thorougbred Basic main system. We use their 32 bit ODBC driver
and they have not released a 64 bit driver. These jobs are done mostly
through DTS and I was preparing to re-write all these existing
programs in SSIS, but I just realized that the new 64 bit edidtion of
SQL Server can't see the legacy 32 bit driver. Is there any work-
around for this in SSIS? Most companies are releasing 64 bit
compatable drivers (DB2, JODBC etc). I have tried entering the keys in
the registry etc. to no avail. I need to create a SYSTEM DSN.
Again, we have to use ODBC.
Thanks in advance,
Kristina
Hi Kristina
"Kristina" wrote:
> We are preparing to migrate all our 2000 databases to SQL Server 2005
> and have run into an unexpected issue. We pull in a lot of Legacy Data
> from a Thorougbred Basic main system. We use their 32 bit ODBC driver
> and they have not released a 64 bit driver. These jobs are done mostly
> through DTS and I was preparing to re-write all these existing
> programs in SSIS, but I just realized that the new 64 bit edidtion of
> SQL Server can't see the legacy 32 bit driver. Is there any work-
> around for this in SSIS? Most companies are releasing 64 bit
> compatable drivers (DB2, JODBC etc). I have tried entering the keys in
> the registry etc. to no avail. I need to create a SYSTEM DSN.
> Again, we have to use ODBC.
> Thanks in advance,
> Kristina
Are you using the 32bit ODBC administrator c:\windows\syswow64\odbcad32.exe
to set up the DSN. I am not sure if that would allow you to configure a DSN
that you can use!
John
|||On Mar 14, 2:25 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi Kristina
>
>
> "Kristina" wrote:
>
> Are you using the 32bit ODBC administrator c:\windows\syswow64\odbcad32.exe
> to set up the DSN. I am not sure if that would allow you to configure a DSN
> that you can use!
> John- Hide quoted text -
> - Show quoted text -
Yes, I was able to do that, but the functionality is not availible
when I go to choose a .NET connection (ODBC) in SSIS. In the old DTS,
it would show me the fields, lengths etc and everything in the
database. I also haven't been able to get a linked server set up
which we need for open rowset queries.
|||You're right, there is no 64 bit Jet driver; so you cannot directly use an
ODBC connection to JET from a program running under the 64 bit mode.
However, to solve your problem, you have many other possibilities:
1- Run your DTS package from a second machine (could be a virtual machine),
preferably one with SQL-Server 2000 but you could also use an installation
of SQL-Server 2005 running in 32 bit mode and with the special add-in for
using DTS installed.
Of course, instead of using a second machine or a virtual one, you could
also install a second instance of SQL-Server (either 2000 or 2005) but this
time running under the 32 bit mode.
2- Same thing than 1) with an installation of SQL-Server 2005 running in 32
bit mode but now with SSIS instead of DTS.
3- Use the 32 bit version of dtexec.exe (the one that you will find under
the "Program Files (x86)" folder branch). However, I don't know if you must
have an installation of SQL-Server running under 32 bit to do that or if you
can also execute DTS package.
4- Install SQL-Server 2005 Express and make your calls from SQL-Server 2005
64 bit to the 32 bit ODBC driver through it, see:
http://gorm-braarvig.blogspot.com/2005/11/access-database-from-sql-200564.html
In your case, as you have already all the necessary DTS packages, I would
use them with an instance of SQL-Server running in 32 bit mode on one side
and SQL-Server 2005 64 bit on the other side as the target instead of
rewriting them with SSIS.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Kristina" <KristinaDBA@.gmail.com> wrote in message
news:1173886263.138387.294260@.d57g2000hsg.googlegr oups.com...
> We are preparing to migrate all our 2000 databases to SQL Server 2005
> and have run into an unexpected issue. We pull in a lot of Legacy Data
> from a Thorougbred Basic main system. We use their 32 bit ODBC driver
> and they have not released a 64 bit driver. These jobs are done mostly
> through DTS and I was preparing to re-write all these existing
> programs in SSIS, but I just realized that the new 64 bit edidtion of
> SQL Server can't see the legacy 32 bit driver. Is there any work-
> around for this in SSIS? Most companies are releasing 64 bit
> compatable drivers (DB2, JODBC etc). I have tried entering the keys in
> the registry etc. to no avail. I need to create a SYSTEM DSN.
> Again, we have to use ODBC.
> Thanks in advance,
> Kristina
>
|||On Mar 14, 5:52 pm, "Sylvain Lafontaine" <sylvain aei ca (fill the
blanks, no spam please)> wrote:
> You're right, there is no 64 bit Jet driver; so you cannot directly use an
> ODBC connection to JET from a program running under the 64 bit mode.
> However, to solve your problem, you have many other possibilities:
> 1- Run your DTS package from a second machine (could be a virtual machine),
> preferably one with SQL-Server 2000 but you could also use an installation
> of SQL-Server 2005 running in 32 bit mode and with the special add-in for
> using DTS installed.
> Of course, instead of using a second machine or a virtual one, you could
> also install a second instance of SQL-Server (either 2000 or 2005) but this
> time running under the 32 bit mode.
> 2- Same thing than 1) with an installation of SQL-Server 2005 running in 32
> bit mode but now with SSIS instead of DTS.
> 3- Use the 32 bit version of dtexec.exe (the one that you will find under
> the "Program Files (x86)" folder branch). However, I don't know if you must
> have an installation of SQL-Server running under 32 bit to do that or if you
> can also execute DTS package.
> 4- Install SQL-Server 2005 Express and make your calls from SQL-Server 2005
> 64 bit to the 32 bit ODBC driver through it, see:
> http://gorm-braarvig.blogspot.com/2005/11/access-database-from-sql-20...
> In your case, as you have already all the necessary DTS packages, I would
> use them with an instance of SQL-Server running in 32 bit mode on one side
> and SQL-Server 2005 64 bit on the other side as the target instead of
> rewriting them with SSIS.
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
> "Kristina" <Kristina...@.gmail.com> wrote in message
> news:1173886263.138387.294260@.d57g2000hsg.googlegr oups.com...
>
>
>
> - Show quoted text -
thanks for all the good advice. You clarified the problem for me. I
think we are leaning toward using the DTS on the 32 bit 2000 machine
and then replicating the data from 2000 to 2005. That brings up
another point..Am I correct in assuming that replication (probably
transactional) from SQL Server 2000 32 bit to SQL Server 2005 64 bit
would work? I would do a pull subscription from the 2005 box to the
2000 box.
|||Can't DTS connect to 64-bit SQL 2005?
Why couldn't you just move the data directly to 2005,
just use the 2000 DTS?
>
> thanks for all the good advice. You clarified the problem for me. I
> think we are leaning toward using the DTS on the 32 bit 2000 machine
> and then replicating the data from 2000 to 2005. That brings up
> another point..Am I correct in assuming that replication (probably
> transactional) from SQL Server 2000 32 bit to SQL Server 2005 64 bit
> would work? I would do a pull subscription from the 2005 box to the
> 2000 box.- Hide quoted text -
> - Show quoted text -
|||On Mar 15, 10:02 am, "raibeart" <raibe...@.gmail.com> wrote:
> Can't DTS connect to 64-bit SQL 2005?
> Why couldn't you just move the data directly to 2005,
> just use the 2000 DTS?
>
>
>
>
> - Show quoted text -
I see your point but DTS is a lot more cumbersome than the new SSIS
and hard to keep updated. We move approx 20 gigs of data on a nightly
basis and then transform it into other relational databases. We don't
want to be forced to use the legacy DTS on the 64 bit 2005 SQL
edition. If we have to use the DTS we will keep it on an older 2000
box.
|||Hi Kristina
"Kristina" wrote:
> On Mar 14, 5:52 pm, "Sylvain Lafontaine" <sylvain aei ca (fill the
> blanks, no spam please)> wrote:
>
> thanks for all the good advice. You clarified the problem for me. I
> think we are leaning toward using the DTS on the 32 bit 2000 machine
> and then replicating the data from 2000 to 2005. That brings up
> another point..Am I correct in assuming that replication (probably
> transactional) from SQL Server 2000 32 bit to SQL Server 2005 64 bit
> would work? I would do a pull subscription from the 2005 box to the
> 2000 box.
>
I would not expect you do need to use replication, as you can define an ODBC
datasource on your 32 bit instance that connects to your SQL 2005 server.
John
|||Even if you use the 2000 DTS or the 2005 SSIS, you don't have to first ship
the data to SQL-Server 2000 or 2005 running in 32 bit mode, you can send
them directly to the SQL-Server 2005 running in 64 bit mode using either DTS
or SSIS 32 bit. The only obligation is that the program making the
connection with the 32 bit ODBC driver must be running itself in the 32 bit
mode but that doesn't forbid it to communicate directly with the SQL-Server
2005 64 bit at the other side of the transformation.
Also, even if you are running DTS on a SQL-Server 2000 or SSIS on a 32 bit
version of SQL-Server 2005, you don't have to send or retrieve data from one
of these two servers; are they are used only as the host for running the DTS
or the SSIS programs in 32 bit mode.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Kristina" <KristinaDBA@.gmail.com> wrote in message
news:1173967890.365144.154580@.l75g2000hse.googlegr oups.com...
> On Mar 15, 10:02 am, "raibeart" <raibe...@.gmail.com> wrote:
> I see your point but DTS is a lot more cumbersome than the new SSIS
> and hard to keep updated. We move approx 20 gigs of data on a nightly
> basis and then transform it into other relational databases. We don't
> want to be forced to use the legacy DTS on the 64 bit 2005 SQL
> edition. If we have to use the DTS we will keep it on an older 2000
> box.
>
|||On Mar 15, 10:20 am, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi Kristina
>
>
> "Kristina" wrote:
>
>
>
>
>
>
>
>
> I would not expect you do need to use replication, as you can define an ODBC
> datasource on your 32 bit instance that connects to your SQL 2005 server.
> John- Hide quoted text -
> - Show quoted text -
You guys are right. Thanks for the help!
Talk to you later with my next crisis moment....
and have run into an unexpected issue. We pull in a lot of Legacy Data
from a Thorougbred Basic main system. We use their 32 bit ODBC driver
and they have not released a 64 bit driver. These jobs are done mostly
through DTS and I was preparing to re-write all these existing
programs in SSIS, but I just realized that the new 64 bit edidtion of
SQL Server can't see the legacy 32 bit driver. Is there any work-
around for this in SSIS? Most companies are releasing 64 bit
compatable drivers (DB2, JODBC etc). I have tried entering the keys in
the registry etc. to no avail. I need to create a SYSTEM DSN.
Again, we have to use ODBC.
Thanks in advance,
Kristina
Hi Kristina
"Kristina" wrote:
> We are preparing to migrate all our 2000 databases to SQL Server 2005
> and have run into an unexpected issue. We pull in a lot of Legacy Data
> from a Thorougbred Basic main system. We use their 32 bit ODBC driver
> and they have not released a 64 bit driver. These jobs are done mostly
> through DTS and I was preparing to re-write all these existing
> programs in SSIS, but I just realized that the new 64 bit edidtion of
> SQL Server can't see the legacy 32 bit driver. Is there any work-
> around for this in SSIS? Most companies are releasing 64 bit
> compatable drivers (DB2, JODBC etc). I have tried entering the keys in
> the registry etc. to no avail. I need to create a SYSTEM DSN.
> Again, we have to use ODBC.
> Thanks in advance,
> Kristina
Are you using the 32bit ODBC administrator c:\windows\syswow64\odbcad32.exe
to set up the DSN. I am not sure if that would allow you to configure a DSN
that you can use!
John
|||On Mar 14, 2:25 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi Kristina
>
>
> "Kristina" wrote:
>
> Are you using the 32bit ODBC administrator c:\windows\syswow64\odbcad32.exe
> to set up the DSN. I am not sure if that would allow you to configure a DSN
> that you can use!
> John- Hide quoted text -
> - Show quoted text -
Yes, I was able to do that, but the functionality is not availible
when I go to choose a .NET connection (ODBC) in SSIS. In the old DTS,
it would show me the fields, lengths etc and everything in the
database. I also haven't been able to get a linked server set up
which we need for open rowset queries.
|||You're right, there is no 64 bit Jet driver; so you cannot directly use an
ODBC connection to JET from a program running under the 64 bit mode.
However, to solve your problem, you have many other possibilities:
1- Run your DTS package from a second machine (could be a virtual machine),
preferably one with SQL-Server 2000 but you could also use an installation
of SQL-Server 2005 running in 32 bit mode and with the special add-in for
using DTS installed.
Of course, instead of using a second machine or a virtual one, you could
also install a second instance of SQL-Server (either 2000 or 2005) but this
time running under the 32 bit mode.
2- Same thing than 1) with an installation of SQL-Server 2005 running in 32
bit mode but now with SSIS instead of DTS.
3- Use the 32 bit version of dtexec.exe (the one that you will find under
the "Program Files (x86)" folder branch). However, I don't know if you must
have an installation of SQL-Server running under 32 bit to do that or if you
can also execute DTS package.
4- Install SQL-Server 2005 Express and make your calls from SQL-Server 2005
64 bit to the 32 bit ODBC driver through it, see:
http://gorm-braarvig.blogspot.com/2005/11/access-database-from-sql-200564.html
In your case, as you have already all the necessary DTS packages, I would
use them with an instance of SQL-Server running in 32 bit mode on one side
and SQL-Server 2005 64 bit on the other side as the target instead of
rewriting them with SSIS.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Kristina" <KristinaDBA@.gmail.com> wrote in message
news:1173886263.138387.294260@.d57g2000hsg.googlegr oups.com...
> We are preparing to migrate all our 2000 databases to SQL Server 2005
> and have run into an unexpected issue. We pull in a lot of Legacy Data
> from a Thorougbred Basic main system. We use their 32 bit ODBC driver
> and they have not released a 64 bit driver. These jobs are done mostly
> through DTS and I was preparing to re-write all these existing
> programs in SSIS, but I just realized that the new 64 bit edidtion of
> SQL Server can't see the legacy 32 bit driver. Is there any work-
> around for this in SSIS? Most companies are releasing 64 bit
> compatable drivers (DB2, JODBC etc). I have tried entering the keys in
> the registry etc. to no avail. I need to create a SYSTEM DSN.
> Again, we have to use ODBC.
> Thanks in advance,
> Kristina
>
|||On Mar 14, 5:52 pm, "Sylvain Lafontaine" <sylvain aei ca (fill the
blanks, no spam please)> wrote:
> You're right, there is no 64 bit Jet driver; so you cannot directly use an
> ODBC connection to JET from a program running under the 64 bit mode.
> However, to solve your problem, you have many other possibilities:
> 1- Run your DTS package from a second machine (could be a virtual machine),
> preferably one with SQL-Server 2000 but you could also use an installation
> of SQL-Server 2005 running in 32 bit mode and with the special add-in for
> using DTS installed.
> Of course, instead of using a second machine or a virtual one, you could
> also install a second instance of SQL-Server (either 2000 or 2005) but this
> time running under the 32 bit mode.
> 2- Same thing than 1) with an installation of SQL-Server 2005 running in 32
> bit mode but now with SSIS instead of DTS.
> 3- Use the 32 bit version of dtexec.exe (the one that you will find under
> the "Program Files (x86)" folder branch). However, I don't know if you must
> have an installation of SQL-Server running under 32 bit to do that or if you
> can also execute DTS package.
> 4- Install SQL-Server 2005 Express and make your calls from SQL-Server 2005
> 64 bit to the 32 bit ODBC driver through it, see:
> http://gorm-braarvig.blogspot.com/2005/11/access-database-from-sql-20...
> In your case, as you have already all the necessary DTS packages, I would
> use them with an instance of SQL-Server running in 32 bit mode on one side
> and SQL-Server 2005 64 bit on the other side as the target instead of
> rewriting them with SSIS.
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
> "Kristina" <Kristina...@.gmail.com> wrote in message
> news:1173886263.138387.294260@.d57g2000hsg.googlegr oups.com...
>
>
>
> - Show quoted text -
thanks for all the good advice. You clarified the problem for me. I
think we are leaning toward using the DTS on the 32 bit 2000 machine
and then replicating the data from 2000 to 2005. That brings up
another point..Am I correct in assuming that replication (probably
transactional) from SQL Server 2000 32 bit to SQL Server 2005 64 bit
would work? I would do a pull subscription from the 2005 box to the
2000 box.
|||Can't DTS connect to 64-bit SQL 2005?
Why couldn't you just move the data directly to 2005,
just use the 2000 DTS?
>
> thanks for all the good advice. You clarified the problem for me. I
> think we are leaning toward using the DTS on the 32 bit 2000 machine
> and then replicating the data from 2000 to 2005. That brings up
> another point..Am I correct in assuming that replication (probably
> transactional) from SQL Server 2000 32 bit to SQL Server 2005 64 bit
> would work? I would do a pull subscription from the 2005 box to the
> 2000 box.- Hide quoted text -
> - Show quoted text -
|||On Mar 15, 10:02 am, "raibeart" <raibe...@.gmail.com> wrote:
> Can't DTS connect to 64-bit SQL 2005?
> Why couldn't you just move the data directly to 2005,
> just use the 2000 DTS?
>
>
>
>
> - Show quoted text -
I see your point but DTS is a lot more cumbersome than the new SSIS
and hard to keep updated. We move approx 20 gigs of data on a nightly
basis and then transform it into other relational databases. We don't
want to be forced to use the legacy DTS on the 64 bit 2005 SQL
edition. If we have to use the DTS we will keep it on an older 2000
box.
|||Hi Kristina
"Kristina" wrote:
> On Mar 14, 5:52 pm, "Sylvain Lafontaine" <sylvain aei ca (fill the
> blanks, no spam please)> wrote:
>
> thanks for all the good advice. You clarified the problem for me. I
> think we are leaning toward using the DTS on the 32 bit 2000 machine
> and then replicating the data from 2000 to 2005. That brings up
> another point..Am I correct in assuming that replication (probably
> transactional) from SQL Server 2000 32 bit to SQL Server 2005 64 bit
> would work? I would do a pull subscription from the 2005 box to the
> 2000 box.
>
I would not expect you do need to use replication, as you can define an ODBC
datasource on your 32 bit instance that connects to your SQL 2005 server.
John
|||Even if you use the 2000 DTS or the 2005 SSIS, you don't have to first ship
the data to SQL-Server 2000 or 2005 running in 32 bit mode, you can send
them directly to the SQL-Server 2005 running in 64 bit mode using either DTS
or SSIS 32 bit. The only obligation is that the program making the
connection with the 32 bit ODBC driver must be running itself in the 32 bit
mode but that doesn't forbid it to communicate directly with the SQL-Server
2005 64 bit at the other side of the transformation.
Also, even if you are running DTS on a SQL-Server 2000 or SSIS on a 32 bit
version of SQL-Server 2005, you don't have to send or retrieve data from one
of these two servers; are they are used only as the host for running the DTS
or the SSIS programs in 32 bit mode.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Kristina" <KristinaDBA@.gmail.com> wrote in message
news:1173967890.365144.154580@.l75g2000hse.googlegr oups.com...
> On Mar 15, 10:02 am, "raibeart" <raibe...@.gmail.com> wrote:
> I see your point but DTS is a lot more cumbersome than the new SSIS
> and hard to keep updated. We move approx 20 gigs of data on a nightly
> basis and then transform it into other relational databases. We don't
> want to be forced to use the legacy DTS on the 64 bit 2005 SQL
> edition. If we have to use the DTS we will keep it on an older 2000
> box.
>
|||On Mar 15, 10:20 am, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi Kristina
>
>
> "Kristina" wrote:
>
>
>
>
>
>
>
>
> I would not expect you do need to use replication, as you can define an ODBC
> datasource on your 32 bit instance that connects to your SQL 2005 server.
> John- Hide quoted text -
> - Show quoted text -
You guys are right. Thanks for the help!
Talk to you later with my next crisis moment....
ODBC on 64 bit Enterprise SQL Server 2005
We are preparing to migrate all our 2000 databases to SQL Server 2005
and have run into an unexpected issue. We pull in a lot of Legacy Data
from a Thorougbred Basic main system. We use their 32 bit ODBC driver
and they have not released a 64 bit driver. These jobs are done mostly
through DTS and I was preparing to re-write all these existing
programs in SSIS, but I just realized that the new 64 bit edidtion of
SQL Server can't see the legacy 32 bit driver. Is there any work-
around for this in SSIS? Most companies are releasing 64 bit
compatable drivers (DB2, JODBC etc). I have tried entering the keys in
the registry etc. to no avail. I need to create a SYSTEM DSN.
Again, we have to use ODBC.
Thanks in advance,
KristinaHi Kristina
"Kristina" wrote:
> We are preparing to migrate all our 2000 databases to SQL Server 2005
> and have run into an unexpected issue. We pull in a lot of Legacy Data
> from a Thorougbred Basic main system. We use their 32 bit ODBC driver
> and they have not released a 64 bit driver. These jobs are done mostly
> through DTS and I was preparing to re-write all these existing
> programs in SSIS, but I just realized that the new 64 bit edidtion of
> SQL Server can't see the legacy 32 bit driver. Is there any work-
> around for this in SSIS? Most companies are releasing 64 bit
> compatable drivers (DB2, JODBC etc). I have tried entering the keys in
> the registry etc. to no avail. I need to create a SYSTEM DSN.
> Again, we have to use ODBC.
> Thanks in advance,
> Kristina
Are you using the 32bit ODBC administrator c:\windows\syswow64\odbcad32.exe
to set up the DSN. I am not sure if that would allow you to configure a DSN
that you can use!
John|||On Mar 14, 2:25 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi Kristina
>
>
> "Kristina" wrote:
>
>
> Are you using the 32bit ODBC administrator c:\windows\syswow64\odbcad32.ex
e
> to set up the DSN. I am not sure if that would allow you to configure a DS
N
> that you can use!
> John- Hide quoted text -
> - Show quoted text -
Yes, I was able to do that, but the functionality is not availible
when I go to choose a .NET connection (ODBC) in SSIS. In the old DTS,
it would show me the fields, lengths etc and everything in the
database. I also haven't been able to get a linked server set up
which we need for open rowset queries.|||You're right, there is no 64 bit Jet driver; so you cannot directly use an
ODBC connection to JET from a program running under the 64 bit mode.
However, to solve your problem, you have many other possibilities:
1- Run your DTS package from a second machine (could be a virtual machine),
preferably one with SQL-Server 2000 but you could also use an installation
of SQL-Server 2005 running in 32 bit mode and with the special add-in for
using DTS installed.
Of course, instead of using a second machine or a virtual one, you could
also install a second instance of SQL-Server (either 2000 or 2005) but this
time running under the 32 bit mode.
2- Same thing than 1) with an installation of SQL-Server 2005 running in 32
bit mode but now with SSIS instead of DTS.
3- Use the 32 bit version of dtexec.exe (the one that you will find under
the "Program Files (x86)" folder branch). However, I don't know if you must
have an installation of SQL-Server running under 32 bit to do that or if you
can also execute DTS package.
4- Install SQL-Server 2005 Express and make your calls from SQL-Server 2005
64 bit to the 32 bit ODBC driver through it, see:
[url]http://gorm-braarvig.blogspot.com/2005/11/access-database-from-sql-200564.html[/ur
l]
In your case, as you have already all the necessary DTS packages, I would
use them with an instance of SQL-Server running in 32 bit mode on one side
and SQL-Server 2005 64 bit on the other side as the target instead of
rewriting them with SSIS.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Kristina" <KristinaDBA@.gmail.com> wrote in message
news:1173886263.138387.294260@.d57g2000hsg.googlegroups.com...
> We are preparing to migrate all our 2000 databases to SQL Server 2005
> and have run into an unexpected issue. We pull in a lot of Legacy Data
> from a Thorougbred Basic main system. We use their 32 bit ODBC driver
> and they have not released a 64 bit driver. These jobs are done mostly
> through DTS and I was preparing to re-write all these existing
> programs in SSIS, but I just realized that the new 64 bit edidtion of
> SQL Server can't see the legacy 32 bit driver. Is there any work-
> around for this in SSIS? Most companies are releasing 64 bit
> compatable drivers (DB2, JODBC etc). I have tried entering the keys in
> the registry etc. to no avail. I need to create a SYSTEM DSN.
> Again, we have to use ODBC.
> Thanks in advance,
> Kristina
>|||On Mar 14, 5:52 pm, "Sylvain Lafontaine" <sylvain aei ca (fill the
blanks, no spam please)> wrote:
> You're right, there is no 64 bit Jet driver; so you cannot directly use an
> ODBC connection to JET from a program running under the 64 bit mode.
> However, to solve your problem, you have many other possibilities:
> 1- Run your DTS package from a second machine (could be a virtual machine)
,
> preferably one with SQL-Server 2000 but you could also use an installation
> of SQL-Server 2005 running in 32 bit mode and with the special add-in for
> using DTS installed.
> Of course, instead of using a second machine or a virtual one, you cou
ld
> also install a second instance of SQL-Server (either 2000 or 2005) but thi
s
> time running under the 32 bit mode.
> 2- Same thing than 1) with an installation of SQL-Server 2005 running in 3
2
> bit mode but now with SSIS instead of DTS.
> 3- Use the 32 bit version of dtexec.exe (the one that you will find under
> the "Program Files (x86)" folder branch). However, I don't know if you mu
st
> have an installation of SQL-Server running under 32 bit to do that or if y
ou
> can also execute DTS package.
> 4- Install SQL-Server 2005 Express and make your calls from SQL-Server 200
5
> 64 bit to the 32 bit ODBC driver through it, see:
> http://gorm-braarvig.blogspot.com/2...-from-sql-20...
> In your case, as you have already all the necessary DTS packages, I would
> use them with an instance of SQL-Server running in 32 bit mode on one side
> and SQL-Server 2005 64 bit on the other side as the target instead of
> rewriting them with SSIS.
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
> "Kristina" <Kristina...@.gmail.com> wrote in message
> news:1173886263.138387.294260@.d57g2000hsg.googlegroups.com...
>
>
>
>
> - Show quoted text -
thanks for all the good advice. You clarified the problem for me. I
think we are leaning toward using the DTS on the 32 bit 2000 machine
and then replicating the data from 2000 to 2005. That brings up
another point..Am I correct in assuming that replication (probably
transactional) from SQL Server 2000 32 bit to SQL Server 2005 64 bit
would work? I would do a pull subscription from the 2005 box to the
2000 box.|||Can't DTS connect to 64-bit SQL 2005?
Why couldn't you just move the data directly to 2005,
just use the 2000 DTS?
>
> thanks for all the good advice. You clarified the problem for me. I
> think we are leaning toward using the DTS on the 32 bit 2000 machine
> and then replicating the data from 2000 to 2005. That brings up
> another point..Am I correct in assuming that replication (probably
> transactional) from SQL Server 2000 32 bit to SQL Server 2005 64 bit
> would work? I would do a pull subscription from the 2005 box to the
> 2000 box.- Hide quoted text -
> - Show quoted text -|||On Mar 15, 10:02 am, "raibeart" <raibe...@.gmail.com> wrote:
> Can't DTS connect to 64-bit SQL 2005?
> Why couldn't you just move the data directly to 2005,
> just use the 2000 DTS?
>
>
>
>
>
> - Show quoted text -
I see your point but DTS is a lot more cumbersome than the new SSIS
and hard to keep updated. We move approx 20 gigs of data on a nightly
basis and then transform it into other relational databases. We don't
want to be forced to use the legacy DTS on the 64 bit 2005 SQL
edition. If we have to use the DTS we will keep it on an older 2000
box.|||Hi Kristina
"Kristina" wrote:
> On Mar 14, 5:52 pm, "Sylvain Lafontaine" <sylvain aei ca (fill the
> blanks, no spam please)> wrote:
>
> thanks for all the good advice. You clarified the problem for me. I
> think we are leaning toward using the DTS on the 32 bit 2000 machine
> and then replicating the data from 2000 to 2005. That brings up
> another point..Am I correct in assuming that replication (probably
> transactional) from SQL Server 2000 32 bit to SQL Server 2005 64 bit
> would work? I would do a pull subscription from the 2005 box to the
> 2000 box.
>
I would not expect you do need to use replication, as you can define an ODBC
datasource on your 32 bit instance that connects to your SQL 2005 server.
John|||Even if you use the 2000 DTS or the 2005 SSIS, you don't have to first ship
the data to SQL-Server 2000 or 2005 running in 32 bit mode, you can send
them directly to the SQL-Server 2005 running in 64 bit mode using either DTS
or SSIS 32 bit. The only obligation is that the program making the
connection with the 32 bit ODBC driver must be running itself in the 32 bit
mode but that doesn't forbid it to communicate directly with the SQL-Server
2005 64 bit at the other side of the transformation.
Also, even if you are running DTS on a SQL-Server 2000 or SSIS on a 32 bit
version of SQL-Server 2005, you don't have to send or retrieve data from one
of these two servers; are they are used only as the host for running the DTS
or the SSIS programs in 32 bit mode.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Kristina" <KristinaDBA@.gmail.com> wrote in message
news:1173967890.365144.154580@.l75g2000hse.googlegroups.com...
> On Mar 15, 10:02 am, "raibeart" <raibe...@.gmail.com> wrote:
> I see your point but DTS is a lot more cumbersome than the new SSIS
> and hard to keep updated. We move approx 20 gigs of data on a nightly
> basis and then transform it into other relational databases. We don't
> want to be forced to use the legacy DTS on the 64 bit 2005 SQL
> edition. If we have to use the DTS we will keep it on an older 2000
> box.
>|||On Mar 15, 10:20 am, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi Kristina
>
>
> "Kristina" wrote:
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> I would not expect you do need to use replication, as you can define an OD
BC
> datasource on your 32 bit instance that connects to your SQL 2005 server.
> John- Hide quoted text -
> - Show quoted text -
You guys are right. Thanks for the help!
Talk to you later with my next crisis moment....
and have run into an unexpected issue. We pull in a lot of Legacy Data
from a Thorougbred Basic main system. We use their 32 bit ODBC driver
and they have not released a 64 bit driver. These jobs are done mostly
through DTS and I was preparing to re-write all these existing
programs in SSIS, but I just realized that the new 64 bit edidtion of
SQL Server can't see the legacy 32 bit driver. Is there any work-
around for this in SSIS? Most companies are releasing 64 bit
compatable drivers (DB2, JODBC etc). I have tried entering the keys in
the registry etc. to no avail. I need to create a SYSTEM DSN.
Again, we have to use ODBC.
Thanks in advance,
KristinaHi Kristina
"Kristina" wrote:
> We are preparing to migrate all our 2000 databases to SQL Server 2005
> and have run into an unexpected issue. We pull in a lot of Legacy Data
> from a Thorougbred Basic main system. We use their 32 bit ODBC driver
> and they have not released a 64 bit driver. These jobs are done mostly
> through DTS and I was preparing to re-write all these existing
> programs in SSIS, but I just realized that the new 64 bit edidtion of
> SQL Server can't see the legacy 32 bit driver. Is there any work-
> around for this in SSIS? Most companies are releasing 64 bit
> compatable drivers (DB2, JODBC etc). I have tried entering the keys in
> the registry etc. to no avail. I need to create a SYSTEM DSN.
> Again, we have to use ODBC.
> Thanks in advance,
> Kristina
Are you using the 32bit ODBC administrator c:\windows\syswow64\odbcad32.exe
to set up the DSN. I am not sure if that would allow you to configure a DSN
that you can use!
John|||On Mar 14, 2:25 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi Kristina
>
>
> "Kristina" wrote:
>
>
> Are you using the 32bit ODBC administrator c:\windows\syswow64\odbcad32.ex
e
> to set up the DSN. I am not sure if that would allow you to configure a DS
N
> that you can use!
> John- Hide quoted text -
> - Show quoted text -
Yes, I was able to do that, but the functionality is not availible
when I go to choose a .NET connection (ODBC) in SSIS. In the old DTS,
it would show me the fields, lengths etc and everything in the
database. I also haven't been able to get a linked server set up
which we need for open rowset queries.|||You're right, there is no 64 bit Jet driver; so you cannot directly use an
ODBC connection to JET from a program running under the 64 bit mode.
However, to solve your problem, you have many other possibilities:
1- Run your DTS package from a second machine (could be a virtual machine),
preferably one with SQL-Server 2000 but you could also use an installation
of SQL-Server 2005 running in 32 bit mode and with the special add-in for
using DTS installed.
Of course, instead of using a second machine or a virtual one, you could
also install a second instance of SQL-Server (either 2000 or 2005) but this
time running under the 32 bit mode.
2- Same thing than 1) with an installation of SQL-Server 2005 running in 32
bit mode but now with SSIS instead of DTS.
3- Use the 32 bit version of dtexec.exe (the one that you will find under
the "Program Files (x86)" folder branch). However, I don't know if you must
have an installation of SQL-Server running under 32 bit to do that or if you
can also execute DTS package.
4- Install SQL-Server 2005 Express and make your calls from SQL-Server 2005
64 bit to the 32 bit ODBC driver through it, see:
[url]http://gorm-braarvig.blogspot.com/2005/11/access-database-from-sql-200564.html[/ur
l]
In your case, as you have already all the necessary DTS packages, I would
use them with an instance of SQL-Server running in 32 bit mode on one side
and SQL-Server 2005 64 bit on the other side as the target instead of
rewriting them with SSIS.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Kristina" <KristinaDBA@.gmail.com> wrote in message
news:1173886263.138387.294260@.d57g2000hsg.googlegroups.com...
> We are preparing to migrate all our 2000 databases to SQL Server 2005
> and have run into an unexpected issue. We pull in a lot of Legacy Data
> from a Thorougbred Basic main system. We use their 32 bit ODBC driver
> and they have not released a 64 bit driver. These jobs are done mostly
> through DTS and I was preparing to re-write all these existing
> programs in SSIS, but I just realized that the new 64 bit edidtion of
> SQL Server can't see the legacy 32 bit driver. Is there any work-
> around for this in SSIS? Most companies are releasing 64 bit
> compatable drivers (DB2, JODBC etc). I have tried entering the keys in
> the registry etc. to no avail. I need to create a SYSTEM DSN.
> Again, we have to use ODBC.
> Thanks in advance,
> Kristina
>|||On Mar 14, 5:52 pm, "Sylvain Lafontaine" <sylvain aei ca (fill the
blanks, no spam please)> wrote:
> You're right, there is no 64 bit Jet driver; so you cannot directly use an
> ODBC connection to JET from a program running under the 64 bit mode.
> However, to solve your problem, you have many other possibilities:
> 1- Run your DTS package from a second machine (could be a virtual machine)
,
> preferably one with SQL-Server 2000 but you could also use an installation
> of SQL-Server 2005 running in 32 bit mode and with the special add-in for
> using DTS installed.
> Of course, instead of using a second machine or a virtual one, you cou
ld
> also install a second instance of SQL-Server (either 2000 or 2005) but thi
s
> time running under the 32 bit mode.
> 2- Same thing than 1) with an installation of SQL-Server 2005 running in 3
2
> bit mode but now with SSIS instead of DTS.
> 3- Use the 32 bit version of dtexec.exe (the one that you will find under
> the "Program Files (x86)" folder branch). However, I don't know if you mu
st
> have an installation of SQL-Server running under 32 bit to do that or if y
ou
> can also execute DTS package.
> 4- Install SQL-Server 2005 Express and make your calls from SQL-Server 200
5
> 64 bit to the 32 bit ODBC driver through it, see:
> http://gorm-braarvig.blogspot.com/2...-from-sql-20...
> In your case, as you have already all the necessary DTS packages, I would
> use them with an instance of SQL-Server running in 32 bit mode on one side
> and SQL-Server 2005 64 bit on the other side as the target instead of
> rewriting them with SSIS.
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
> "Kristina" <Kristina...@.gmail.com> wrote in message
> news:1173886263.138387.294260@.d57g2000hsg.googlegroups.com...
>
>
>
>
> - Show quoted text -
thanks for all the good advice. You clarified the problem for me. I
think we are leaning toward using the DTS on the 32 bit 2000 machine
and then replicating the data from 2000 to 2005. That brings up
another point..Am I correct in assuming that replication (probably
transactional) from SQL Server 2000 32 bit to SQL Server 2005 64 bit
would work? I would do a pull subscription from the 2005 box to the
2000 box.|||Can't DTS connect to 64-bit SQL 2005?
Why couldn't you just move the data directly to 2005,
just use the 2000 DTS?
>
> thanks for all the good advice. You clarified the problem for me. I
> think we are leaning toward using the DTS on the 32 bit 2000 machine
> and then replicating the data from 2000 to 2005. That brings up
> another point..Am I correct in assuming that replication (probably
> transactional) from SQL Server 2000 32 bit to SQL Server 2005 64 bit
> would work? I would do a pull subscription from the 2005 box to the
> 2000 box.- Hide quoted text -
> - Show quoted text -|||On Mar 15, 10:02 am, "raibeart" <raibe...@.gmail.com> wrote:
> Can't DTS connect to 64-bit SQL 2005?
> Why couldn't you just move the data directly to 2005,
> just use the 2000 DTS?
>
>
>
>
>
> - Show quoted text -
I see your point but DTS is a lot more cumbersome than the new SSIS
and hard to keep updated. We move approx 20 gigs of data on a nightly
basis and then transform it into other relational databases. We don't
want to be forced to use the legacy DTS on the 64 bit 2005 SQL
edition. If we have to use the DTS we will keep it on an older 2000
box.|||Hi Kristina
"Kristina" wrote:
> On Mar 14, 5:52 pm, "Sylvain Lafontaine" <sylvain aei ca (fill the
> blanks, no spam please)> wrote:
>
> thanks for all the good advice. You clarified the problem for me. I
> think we are leaning toward using the DTS on the 32 bit 2000 machine
> and then replicating the data from 2000 to 2005. That brings up
> another point..Am I correct in assuming that replication (probably
> transactional) from SQL Server 2000 32 bit to SQL Server 2005 64 bit
> would work? I would do a pull subscription from the 2005 box to the
> 2000 box.
>
I would not expect you do need to use replication, as you can define an ODBC
datasource on your 32 bit instance that connects to your SQL 2005 server.
John|||Even if you use the 2000 DTS or the 2005 SSIS, you don't have to first ship
the data to SQL-Server 2000 or 2005 running in 32 bit mode, you can send
them directly to the SQL-Server 2005 running in 64 bit mode using either DTS
or SSIS 32 bit. The only obligation is that the program making the
connection with the 32 bit ODBC driver must be running itself in the 32 bit
mode but that doesn't forbid it to communicate directly with the SQL-Server
2005 64 bit at the other side of the transformation.
Also, even if you are running DTS on a SQL-Server 2000 or SSIS on a 32 bit
version of SQL-Server 2005, you don't have to send or retrieve data from one
of these two servers; are they are used only as the host for running the DTS
or the SSIS programs in 32 bit mode.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Kristina" <KristinaDBA@.gmail.com> wrote in message
news:1173967890.365144.154580@.l75g2000hse.googlegroups.com...
> On Mar 15, 10:02 am, "raibeart" <raibe...@.gmail.com> wrote:
> I see your point but DTS is a lot more cumbersome than the new SSIS
> and hard to keep updated. We move approx 20 gigs of data on a nightly
> basis and then transform it into other relational databases. We don't
> want to be forced to use the legacy DTS on the 64 bit 2005 SQL
> edition. If we have to use the DTS we will keep it on an older 2000
> box.
>|||On Mar 15, 10:20 am, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi Kristina
>
>
> "Kristina" wrote:
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> I would not expect you do need to use replication, as you can define an OD
BC
> datasource on your 32 bit instance that connects to your SQL 2005 server.
> John- Hide quoted text -
> - Show quoted text -
You guys are right. Thanks for the help!
Talk to you later with my next crisis moment....
ODBC on 64 bit Enterprise SQL Server 2005
We are preparing to migrate all our 2000 databases to SQL Server 2005
and have run into an unexpected issue. We pull in a lot of Legacy Data
from a Thorougbred Basic main system. We use their 32 bit ODBC driver
and they have not released a 64 bit driver. These jobs are done mostly
through DTS and I was preparing to re-write all these existing
programs in SSIS, but I just realized that the new 64 bit edidtion of
SQL Server can't see the legacy 32 bit driver. Is there any work-
around for this in SSIS? Most companies are releasing 64 bit
compatable drivers (DB2, JODBC etc). I have tried entering the keys in
the registry etc. to no avail. I need to create a SYSTEM DSN.
Again, we have to use ODBC.
Thanks in advance,
KristinaHi Kristina
"Kristina" wrote:
> We are preparing to migrate all our 2000 databases to SQL Server 2005
> and have run into an unexpected issue. We pull in a lot of Legacy Data
> from a Thorougbred Basic main system. We use their 32 bit ODBC driver
> and they have not released a 64 bit driver. These jobs are done mostly
> through DTS and I was preparing to re-write all these existing
> programs in SSIS, but I just realized that the new 64 bit edidtion of
> SQL Server can't see the legacy 32 bit driver. Is there any work-
> around for this in SSIS? Most companies are releasing 64 bit
> compatable drivers (DB2, JODBC etc). I have tried entering the keys in
> the registry etc. to no avail. I need to create a SYSTEM DSN.
> Again, we have to use ODBC.
> Thanks in advance,
> Kristina
Are you using the 32bit ODBC administrator c:\windows\syswow64\odbcad32.exe
to set up the DSN. I am not sure if that would allow you to configure a DSN
that you can use!
John|||On Mar 14, 2:25 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi Kristina
>
>
> "Kristina" wrote:
> > We are preparing to migrate all our 2000 databases to SQL Server 2005
> > and have run into an unexpected issue. We pull in a lot of Legacy Data
> > from a Thorougbred Basic main system. We use their 32 bit ODBC driver
> > and they have not released a 64 bit driver. These jobs are done mostly
> > through DTS and I was preparing to re-write all these existing
> > programs in SSIS, but I just realized that the new 64 bit edidtion of
> > SQL Server can't see the legacy 32 bit driver. Is there any work-
> > around for this in SSIS? Most companies are releasing 64 bit
> > compatable drivers (DB2, JODBC etc). I have tried entering the keys in
> > the registry etc. to no avail. I need to create a SYSTEM DSN.
> > Again, we have to use ODBC.
> > Thanks in advance,
> > Kristina
> Are you using the 32bit ODBC administrator c:\windows\syswow64\odbcad32.exe
> to set up the DSN. I am not sure if that would allow you to configure a DSN
> that you can use!
> John- Hide quoted text -
> - Show quoted text -
Yes, I was able to do that, but the functionality is not availible
when I go to choose a .NET connection (ODBC) in SSIS. In the old DTS,
it would show me the fields, lengths etc and everything in the
database. I also haven't been able to get a linked server set up
which we need for open rowset queries.|||You're right, there is no 64 bit Jet driver; so you cannot directly use an
ODBC connection to JET from a program running under the 64 bit mode.
However, to solve your problem, you have many other possibilities:
1- Run your DTS package from a second machine (could be a virtual machine),
preferably one with SQL-Server 2000 but you could also use an installation
of SQL-Server 2005 running in 32 bit mode and with the special add-in for
using DTS installed.
Of course, instead of using a second machine or a virtual one, you could
also install a second instance of SQL-Server (either 2000 or 2005) but this
time running under the 32 bit mode.
2- Same thing than 1) with an installation of SQL-Server 2005 running in 32
bit mode but now with SSIS instead of DTS.
3- Use the 32 bit version of dtexec.exe (the one that you will find under
the "Program Files (x86)" folder branch). However, I don't know if you must
have an installation of SQL-Server running under 32 bit to do that or if you
can also execute DTS package.
4- Install SQL-Server 2005 Express and make your calls from SQL-Server 2005
64 bit to the 32 bit ODBC driver through it, see:
http://gorm-braarvig.blogspot.com/2005/11/access-database-from-sql-200564.html
In your case, as you have already all the necessary DTS packages, I would
use them with an instance of SQL-Server running in 32 bit mode on one side
and SQL-Server 2005 64 bit on the other side as the target instead of
rewriting them with SSIS.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Kristina" <KristinaDBA@.gmail.com> wrote in message
news:1173886263.138387.294260@.d57g2000hsg.googlegroups.com...
> We are preparing to migrate all our 2000 databases to SQL Server 2005
> and have run into an unexpected issue. We pull in a lot of Legacy Data
> from a Thorougbred Basic main system. We use their 32 bit ODBC driver
> and they have not released a 64 bit driver. These jobs are done mostly
> through DTS and I was preparing to re-write all these existing
> programs in SSIS, but I just realized that the new 64 bit edidtion of
> SQL Server can't see the legacy 32 bit driver. Is there any work-
> around for this in SSIS? Most companies are releasing 64 bit
> compatable drivers (DB2, JODBC etc). I have tried entering the keys in
> the registry etc. to no avail. I need to create a SYSTEM DSN.
> Again, we have to use ODBC.
> Thanks in advance,
> Kristina
>|||On Mar 14, 5:52 pm, "Sylvain Lafontaine" <sylvain aei ca (fill the
blanks, no spam please)> wrote:
> You're right, there is no 64 bit Jet driver; so you cannot directly use an
> ODBC connection to JET from a program running under the 64 bit mode.
> However, to solve your problem, you have many other possibilities:
> 1- Run your DTS package from a second machine (could be a virtual machine),
> preferably one with SQL-Server 2000 but you could also use an installation
> of SQL-Server 2005 running in 32 bit mode and with the special add-in for
> using DTS installed.
> Of course, instead of using a second machine or a virtual one, you could
> also install a second instance of SQL-Server (either 2000 or 2005) but this
> time running under the 32 bit mode.
> 2- Same thing than 1) with an installation of SQL-Server 2005 running in 32
> bit mode but now with SSIS instead of DTS.
> 3- Use the 32 bit version of dtexec.exe (the one that you will find under
> the "Program Files (x86)" folder branch). However, I don't know if you must
> have an installation of SQL-Server running under 32 bit to do that or if you
> can also execute DTS package.
> 4- Install SQL-Server 2005 Express and make your calls from SQL-Server 2005
> 64 bit to the 32 bit ODBC driver through it, see:
> http://gorm-braarvig.blogspot.com/2005/11/access-database-from-sql-20...
> In your case, as you have already all the necessary DTS packages, I would
> use them with an instance of SQL-Server running in 32 bit mode on one side
> and SQL-Server 2005 64 bit on the other side as the target instead of
> rewriting them with SSIS.
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
> "Kristina" <Kristina...@.gmail.com> wrote in message
> news:1173886263.138387.294260@.d57g2000hsg.googlegroups.com...
>
> > We are preparing to migrate all our 2000 databases to SQL Server 2005
> > and have run into an unexpected issue. We pull in a lot of Legacy Data
> > from a Thorougbred Basic main system. We use their 32 bit ODBC driver
> > and they have not released a 64 bit driver. These jobs are done mostly
> > through DTS and I was preparing to re-write all these existing
> > programs in SSIS, but I just realized that the new 64 bit edidtion of
> > SQL Server can't see the legacy 32 bit driver. Is there any work-
> > around for this in SSIS? Most companies are releasing 64 bit
> > compatable drivers (DB2, JODBC etc). I have tried entering the keys in
> > the registry etc. to no avail. I need to create a SYSTEM DSN.
> > Again, we have to use ODBC.
> > Thanks in advance,
> > Kristina- Hide quoted text -
> - Show quoted text -
thanks for all the good advice. You clarified the problem for me. I
think we are leaning toward using the DTS on the 32 bit 2000 machine
and then replicating the data from 2000 to 2005. That brings up
another point..Am I correct in assuming that replication (probably
transactional) from SQL Server 2000 32 bit to SQL Server 2005 64 bit
would work? I would do a pull subscription from the 2005 box to the
2000 box.|||Can't DTS connect to 64-bit SQL 2005?
Why couldn't you just move the data directly to 2005,
just use the 2000 DTS?
> > - Show quoted text -
> thanks for all the good advice. You clarified the problem for me. I
> think we are leaning toward using the DTS on the 32 bit 2000 machine
> and then replicating the data from 2000 to 2005. That brings up
> another point..Am I correct in assuming that replication (probably
> transactional) from SQL Server 2000 32 bit to SQL Server 2005 64 bit
> would work? I would do a pull subscription from the 2005 box to the
> 2000 box.- Hide quoted text -
> - Show quoted text -|||On Mar 15, 10:02 am, "raibeart" <raibe...@.gmail.com> wrote:
> Can't DTS connect to 64-bit SQL 2005?
> Why couldn't you just move the data directly to 2005,
> just use the 2000 DTS?
>
>
> > > - Show quoted text -
> > thanks for all the good advice. You clarified the problem for me. I
> > think we are leaning toward using the DTS on the 32 bit 2000 machine
> > and then replicating the data from 2000 to 2005. That brings up
> > another point..Am I correct in assuming that replication (probably
> > transactional) from SQL Server 2000 32 bit to SQL Server 2005 64 bit
> > would work? I would do a pull subscription from the 2005 box to the
> > 2000 box.- Hide quoted text -
> > - Show quoted text -- Hide quoted text -
> - Show quoted text -
I see your point but DTS is a lot more cumbersome than the new SSIS
and hard to keep updated. We move approx 20 gigs of data on a nightly
basis and then transform it into other relational databases. We don't
want to be forced to use the legacy DTS on the 64 bit 2005 SQL
edition. If we have to use the DTS we will keep it on an older 2000
box.|||Hi Kristina
"Kristina" wrote:
> On Mar 14, 5:52 pm, "Sylvain Lafontaine" <sylvain aei ca (fill the
> blanks, no spam please)> wrote:
> > You're right, there is no 64 bit Jet driver; so you cannot directly use an
> > ODBC connection to JET from a program running under the 64 bit mode.
> > However, to solve your problem, you have many other possibilities:
> >
> > 1- Run your DTS package from a second machine (could be a virtual machine),
> > preferably one with SQL-Server 2000 but you could also use an installation
> > of SQL-Server 2005 running in 32 bit mode and with the special add-in for
> > using DTS installed.
> >
> > Of course, instead of using a second machine or a virtual one, you could
> > also install a second instance of SQL-Server (either 2000 or 2005) but this
> > time running under the 32 bit mode.
> >
> > 2- Same thing than 1) with an installation of SQL-Server 2005 running in 32
> > bit mode but now with SSIS instead of DTS.
> >
> > 3- Use the 32 bit version of dtexec.exe (the one that you will find under
> > the "Program Files (x86)" folder branch). However, I don't know if you must
> > have an installation of SQL-Server running under 32 bit to do that or if you
> > can also execute DTS package.
> >
> > 4- Install SQL-Server 2005 Express and make your calls from SQL-Server 2005
> > 64 bit to the 32 bit ODBC driver through it, see:
> >
> > http://gorm-braarvig.blogspot.com/2005/11/access-database-from-sql-20...
> >
> > In your case, as you have already all the necessary DTS packages, I would
> > use them with an instance of SQL-Server running in 32 bit mode on one side
> > and SQL-Server 2005 64 bit on the other side as the target instead of
> > rewriting them with SSIS.
> >
> > --
> > Sylvain Lafontaine, ing.
> > MVP - Technologies Virtual-PC
> > E-mail: sylvain aei ca (fill the blanks, no spam please)
> >
> > "Kristina" <Kristina...@.gmail.com> wrote in message
> >
> > news:1173886263.138387.294260@.d57g2000hsg.googlegroups.com...
> >
> >
> >
> > > We are preparing to migrate all our 2000 databases to SQL Server 2005
> > > and have run into an unexpected issue. We pull in a lot of Legacy Data
> > > from a Thorougbred Basic main system. We use their 32 bit ODBC driver
> > > and they have not released a 64 bit driver. These jobs are done mostly
> > > through DTS and I was preparing to re-write all these existing
> > > programs in SSIS, but I just realized that the new 64 bit edidtion of
> > > SQL Server can't see the legacy 32 bit driver. Is there any work-
> > > around for this in SSIS? Most companies are releasing 64 bit
> > > compatable drivers (DB2, JODBC etc). I have tried entering the keys in
> > > the registry etc. to no avail. I need to create a SYSTEM DSN.
> >
> > > Again, we have to use ODBC.
> >
> > > Thanks in advance,
> > > Kristina- Hide quoted text -
> >
> > - Show quoted text -
>
> thanks for all the good advice. You clarified the problem for me. I
> think we are leaning toward using the DTS on the 32 bit 2000 machine
> and then replicating the data from 2000 to 2005. That brings up
> another point..Am I correct in assuming that replication (probably
> transactional) from SQL Server 2000 32 bit to SQL Server 2005 64 bit
> would work? I would do a pull subscription from the 2005 box to the
> 2000 box.
>
I would not expect you do need to use replication, as you can define an ODBC
datasource on your 32 bit instance that connects to your SQL 2005 server.
John|||Even if you use the 2000 DTS or the 2005 SSIS, you don't have to first ship
the data to SQL-Server 2000 or 2005 running in 32 bit mode, you can send
them directly to the SQL-Server 2005 running in 64 bit mode using either DTS
or SSIS 32 bit. The only obligation is that the program making the
connection with the 32 bit ODBC driver must be running itself in the 32 bit
mode but that doesn't forbid it to communicate directly with the SQL-Server
2005 64 bit at the other side of the transformation.
Also, even if you are running DTS on a SQL-Server 2000 or SSIS on a 32 bit
version of SQL-Server 2005, you don't have to send or retrieve data from one
of these two servers; are they are used only as the host for running the DTS
or the SSIS programs in 32 bit mode.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Kristina" <KristinaDBA@.gmail.com> wrote in message
news:1173967890.365144.154580@.l75g2000hse.googlegroups.com...
> On Mar 15, 10:02 am, "raibeart" <raibe...@.gmail.com> wrote:
>> Can't DTS connect to 64-bit SQL 2005?
>> Why couldn't you just move the data directly to 2005,
>> just use the 2000 DTS?
>>
>>
>> > > - Show quoted text -
>> > thanks for all the good advice. You clarified the problem for me. I
>> > think we are leaning toward using the DTS on the 32 bit 2000 machine
>> > and then replicating the data from 2000 to 2005. That brings up
>> > another point..Am I correct in assuming that replication (probably
>> > transactional) from SQL Server 2000 32 bit to SQL Server 2005 64 bit
>> > would work? I would do a pull subscription from the 2005 box to the
>> > 2000 box.- Hide quoted text -
>> > - Show quoted text -- Hide quoted text -
>> - Show quoted text -
> I see your point but DTS is a lot more cumbersome than the new SSIS
> and hard to keep updated. We move approx 20 gigs of data on a nightly
> basis and then transform it into other relational databases. We don't
> want to be forced to use the legacy DTS on the 64 bit 2005 SQL
> edition. If we have to use the DTS we will keep it on an older 2000
> box.
>|||On Mar 15, 10:20 am, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi Kristina
>
>
> "Kristina" wrote:
> > On Mar 14, 5:52 pm, "Sylvain Lafontaine" <sylvain aei ca (fill the
> > blanks, no spam please)> wrote:
> > > You're right, there is no 64 bit Jet driver; so you cannot directly use an
> > > ODBC connection to JET from a program running under the 64 bit mode.
> > > However, to solve your problem, you have many other possibilities:
> > > 1- Run your DTS package from a second machine (could be a virtual machine),
> > > preferably one with SQL-Server 2000 but you could also use an installation
> > > of SQL-Server 2005 running in 32 bit mode and with the special add-in for
> > > using DTS installed.
> > > Of course, instead of using a second machine or a virtual one, you could
> > > also install a second instance of SQL-Server (either 2000 or 2005) but this
> > > time running under the 32 bit mode.
> > > 2- Same thing than 1) with an installation of SQL-Server 2005 running in 32
> > > bit mode but now with SSIS instead of DTS.
> > > 3- Use the 32 bit version of dtexec.exe (the one that you will find under
> > > the "Program Files (x86)" folder branch). However, I don't know if you must
> > > have an installation of SQL-Server running under 32 bit to do that or if you
> > > can also execute DTS package.
> > > 4- Install SQL-Server 2005 Express and make your calls from SQL-Server 2005
> > > 64 bit to the 32 bit ODBC driver through it, see:
> > >http://gorm-braarvig.blogspot.com/2005/11/access-database-from-sql-20...
> > > In your case, as you have already all the necessary DTS packages, I would
> > > use them with an instance of SQL-Server running in 32 bit mode on one side
> > > and SQL-Server 2005 64 bit on the other side as the target instead of
> > > rewriting them with SSIS.
> > > --
> > > Sylvain Lafontaine, ing.
> > > MVP - Technologies Virtual-PC
> > > E-mail: sylvain aei ca (fill the blanks, no spam please)
> > > "Kristina" <Kristina...@.gmail.com> wrote in message
> > >news:1173886263.138387.294260@.d57g2000hsg.googlegroups.com...
> > > > We are preparing to migrate all our 2000 databases to SQL Server 2005
> > > > and have run into an unexpected issue. We pull in a lot of Legacy Data
> > > > from a Thorougbred Basic main system. We use their 32 bit ODBC driver
> > > > and they have not released a 64 bit driver. These jobs are done mostly
> > > > through DTS and I was preparing to re-write all these existing
> > > > programs in SSIS, but I just realized that the new 64 bit edidtion of
> > > > SQL Server can't see the legacy 32 bit driver. Is there any work-
> > > > around for this in SSIS? Most companies are releasing 64 bit
> > > > compatable drivers (DB2, JODBC etc). I have tried entering the keys in
> > > > the registry etc. to no avail. I need to create a SYSTEM DSN.
> > > > Again, we have to use ODBC.
> > > > Thanks in advance,
> > > > Kristina- Hide quoted text -
> > > - Show quoted text -
> > thanks for all the good advice. You clarified the problem for me. I
> > think we are leaning toward using the DTS on the 32 bit 2000 machine
> > and then replicating the data from 2000 to 2005. That brings up
> > another point..Am I correct in assuming that replication (probably
> > transactional) from SQL Server 2000 32 bit to SQL Server 2005 64 bit
> > would work? I would do a pull subscription from the 2005 box to the
> > 2000 box.
> I would not expect you do need to use replication, as you can define an ODBC
> datasource on your 32 bit instance that connects to your SQL 2005 server.
> John- Hide quoted text -
> - Show quoted text -
You guys are right. Thanks for the help!
Talk to you later with my next crisis moment.... :)|||On Mar 15, 12:59 pm, "Sylvain Lafontaine" <sylvain aei ca (fill the
blanks, no spam please)> wrote:
> Even if you use the 2000 DTS or the 2005 SSIS, you don't have to first ship
> the data to SQL-Server 2000 or 2005 running in 32 bit mode, you can send
> them directly to the SQL-Server 2005 running in 64 bit mode using either DTS
> or SSIS 32 bit. The only obligation is that the program making the
> connection with the 32 bit ODBC driver must be running itself in the 32 bit
> mode but that doesn't forbid it to communicate directly with the SQL-Server
> 2005 64 bit at the other side of the transformation.
> Also, even if you are running DTS on a SQL-Server 2000 or SSIS on a 32 bit
> version of SQL-Server 2005, you don't have to send or retrieve data from one
> of these two servers; are they are used only as the host for running the DTS
> or the SSIS programs in 32 bit mode.
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
> "Kristina" <Kristina...@.gmail.com> wrote in message
> news:1173967890.365144.154580@.l75g2000hse.googlegroups.com...
>
> > On Mar 15, 10:02 am, "raibeart" <raibe...@.gmail.com> wrote:
> >> Can't DTS connect to 64-bit SQL 2005?
> >> Why couldn't you just move the data directly to 2005,
> >> just use the 2000 DTS?
> >> > > - Show quoted text -
> >> > thanks for all the good advice. You clarified the problem for me. I
> >> > think we are leaning toward using the DTS on the 32 bit 2000 machine
> >> > and then replicating the data from 2000 to 2005. That brings up
> >> > another point..Am I correct in assuming that replication (probably
> >> > transactional) from SQL Server 2000 32 bit to SQL Server 2005 64 bit
> >> > would work? I would do a pull subscription from the 2005 box to the
> >> > 2000 box.- Hide quoted text -
> >> > - Show quoted text -- Hide quoted text -
> >> - Show quoted text -
> > I see your point but DTS is a lot more cumbersome than the new SSIS
> > and hard to keep updated. We move approx 20 gigs of data on a nightly
> > basis and then transform it into other relational databases. We don't
> > want to be forced to use the legacy DTS on the 64 bit 2005 SQL
> > edition. If we have to use the DTS we will keep it on an older 2000
> > box.- Hide quoted text -
> - Show quoted text -
You guys are right. Thanks for the help!
Talk to you later with my next crisis moment.... :)
and have run into an unexpected issue. We pull in a lot of Legacy Data
from a Thorougbred Basic main system. We use their 32 bit ODBC driver
and they have not released a 64 bit driver. These jobs are done mostly
through DTS and I was preparing to re-write all these existing
programs in SSIS, but I just realized that the new 64 bit edidtion of
SQL Server can't see the legacy 32 bit driver. Is there any work-
around for this in SSIS? Most companies are releasing 64 bit
compatable drivers (DB2, JODBC etc). I have tried entering the keys in
the registry etc. to no avail. I need to create a SYSTEM DSN.
Again, we have to use ODBC.
Thanks in advance,
KristinaHi Kristina
"Kristina" wrote:
> We are preparing to migrate all our 2000 databases to SQL Server 2005
> and have run into an unexpected issue. We pull in a lot of Legacy Data
> from a Thorougbred Basic main system. We use their 32 bit ODBC driver
> and they have not released a 64 bit driver. These jobs are done mostly
> through DTS and I was preparing to re-write all these existing
> programs in SSIS, but I just realized that the new 64 bit edidtion of
> SQL Server can't see the legacy 32 bit driver. Is there any work-
> around for this in SSIS? Most companies are releasing 64 bit
> compatable drivers (DB2, JODBC etc). I have tried entering the keys in
> the registry etc. to no avail. I need to create a SYSTEM DSN.
> Again, we have to use ODBC.
> Thanks in advance,
> Kristina
Are you using the 32bit ODBC administrator c:\windows\syswow64\odbcad32.exe
to set up the DSN. I am not sure if that would allow you to configure a DSN
that you can use!
John|||On Mar 14, 2:25 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi Kristina
>
>
> "Kristina" wrote:
> > We are preparing to migrate all our 2000 databases to SQL Server 2005
> > and have run into an unexpected issue. We pull in a lot of Legacy Data
> > from a Thorougbred Basic main system. We use their 32 bit ODBC driver
> > and they have not released a 64 bit driver. These jobs are done mostly
> > through DTS and I was preparing to re-write all these existing
> > programs in SSIS, but I just realized that the new 64 bit edidtion of
> > SQL Server can't see the legacy 32 bit driver. Is there any work-
> > around for this in SSIS? Most companies are releasing 64 bit
> > compatable drivers (DB2, JODBC etc). I have tried entering the keys in
> > the registry etc. to no avail. I need to create a SYSTEM DSN.
> > Again, we have to use ODBC.
> > Thanks in advance,
> > Kristina
> Are you using the 32bit ODBC administrator c:\windows\syswow64\odbcad32.exe
> to set up the DSN. I am not sure if that would allow you to configure a DSN
> that you can use!
> John- Hide quoted text -
> - Show quoted text -
Yes, I was able to do that, but the functionality is not availible
when I go to choose a .NET connection (ODBC) in SSIS. In the old DTS,
it would show me the fields, lengths etc and everything in the
database. I also haven't been able to get a linked server set up
which we need for open rowset queries.|||You're right, there is no 64 bit Jet driver; so you cannot directly use an
ODBC connection to JET from a program running under the 64 bit mode.
However, to solve your problem, you have many other possibilities:
1- Run your DTS package from a second machine (could be a virtual machine),
preferably one with SQL-Server 2000 but you could also use an installation
of SQL-Server 2005 running in 32 bit mode and with the special add-in for
using DTS installed.
Of course, instead of using a second machine or a virtual one, you could
also install a second instance of SQL-Server (either 2000 or 2005) but this
time running under the 32 bit mode.
2- Same thing than 1) with an installation of SQL-Server 2005 running in 32
bit mode but now with SSIS instead of DTS.
3- Use the 32 bit version of dtexec.exe (the one that you will find under
the "Program Files (x86)" folder branch). However, I don't know if you must
have an installation of SQL-Server running under 32 bit to do that or if you
can also execute DTS package.
4- Install SQL-Server 2005 Express and make your calls from SQL-Server 2005
64 bit to the 32 bit ODBC driver through it, see:
http://gorm-braarvig.blogspot.com/2005/11/access-database-from-sql-200564.html
In your case, as you have already all the necessary DTS packages, I would
use them with an instance of SQL-Server running in 32 bit mode on one side
and SQL-Server 2005 64 bit on the other side as the target instead of
rewriting them with SSIS.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Kristina" <KristinaDBA@.gmail.com> wrote in message
news:1173886263.138387.294260@.d57g2000hsg.googlegroups.com...
> We are preparing to migrate all our 2000 databases to SQL Server 2005
> and have run into an unexpected issue. We pull in a lot of Legacy Data
> from a Thorougbred Basic main system. We use their 32 bit ODBC driver
> and they have not released a 64 bit driver. These jobs are done mostly
> through DTS and I was preparing to re-write all these existing
> programs in SSIS, but I just realized that the new 64 bit edidtion of
> SQL Server can't see the legacy 32 bit driver. Is there any work-
> around for this in SSIS? Most companies are releasing 64 bit
> compatable drivers (DB2, JODBC etc). I have tried entering the keys in
> the registry etc. to no avail. I need to create a SYSTEM DSN.
> Again, we have to use ODBC.
> Thanks in advance,
> Kristina
>|||On Mar 14, 5:52 pm, "Sylvain Lafontaine" <sylvain aei ca (fill the
blanks, no spam please)> wrote:
> You're right, there is no 64 bit Jet driver; so you cannot directly use an
> ODBC connection to JET from a program running under the 64 bit mode.
> However, to solve your problem, you have many other possibilities:
> 1- Run your DTS package from a second machine (could be a virtual machine),
> preferably one with SQL-Server 2000 but you could also use an installation
> of SQL-Server 2005 running in 32 bit mode and with the special add-in for
> using DTS installed.
> Of course, instead of using a second machine or a virtual one, you could
> also install a second instance of SQL-Server (either 2000 or 2005) but this
> time running under the 32 bit mode.
> 2- Same thing than 1) with an installation of SQL-Server 2005 running in 32
> bit mode but now with SSIS instead of DTS.
> 3- Use the 32 bit version of dtexec.exe (the one that you will find under
> the "Program Files (x86)" folder branch). However, I don't know if you must
> have an installation of SQL-Server running under 32 bit to do that or if you
> can also execute DTS package.
> 4- Install SQL-Server 2005 Express and make your calls from SQL-Server 2005
> 64 bit to the 32 bit ODBC driver through it, see:
> http://gorm-braarvig.blogspot.com/2005/11/access-database-from-sql-20...
> In your case, as you have already all the necessary DTS packages, I would
> use them with an instance of SQL-Server running in 32 bit mode on one side
> and SQL-Server 2005 64 bit on the other side as the target instead of
> rewriting them with SSIS.
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
> "Kristina" <Kristina...@.gmail.com> wrote in message
> news:1173886263.138387.294260@.d57g2000hsg.googlegroups.com...
>
> > We are preparing to migrate all our 2000 databases to SQL Server 2005
> > and have run into an unexpected issue. We pull in a lot of Legacy Data
> > from a Thorougbred Basic main system. We use their 32 bit ODBC driver
> > and they have not released a 64 bit driver. These jobs are done mostly
> > through DTS and I was preparing to re-write all these existing
> > programs in SSIS, but I just realized that the new 64 bit edidtion of
> > SQL Server can't see the legacy 32 bit driver. Is there any work-
> > around for this in SSIS? Most companies are releasing 64 bit
> > compatable drivers (DB2, JODBC etc). I have tried entering the keys in
> > the registry etc. to no avail. I need to create a SYSTEM DSN.
> > Again, we have to use ODBC.
> > Thanks in advance,
> > Kristina- Hide quoted text -
> - Show quoted text -
thanks for all the good advice. You clarified the problem for me. I
think we are leaning toward using the DTS on the 32 bit 2000 machine
and then replicating the data from 2000 to 2005. That brings up
another point..Am I correct in assuming that replication (probably
transactional) from SQL Server 2000 32 bit to SQL Server 2005 64 bit
would work? I would do a pull subscription from the 2005 box to the
2000 box.|||Can't DTS connect to 64-bit SQL 2005?
Why couldn't you just move the data directly to 2005,
just use the 2000 DTS?
> > - Show quoted text -
> thanks for all the good advice. You clarified the problem for me. I
> think we are leaning toward using the DTS on the 32 bit 2000 machine
> and then replicating the data from 2000 to 2005. That brings up
> another point..Am I correct in assuming that replication (probably
> transactional) from SQL Server 2000 32 bit to SQL Server 2005 64 bit
> would work? I would do a pull subscription from the 2005 box to the
> 2000 box.- Hide quoted text -
> - Show quoted text -|||On Mar 15, 10:02 am, "raibeart" <raibe...@.gmail.com> wrote:
> Can't DTS connect to 64-bit SQL 2005?
> Why couldn't you just move the data directly to 2005,
> just use the 2000 DTS?
>
>
> > > - Show quoted text -
> > thanks for all the good advice. You clarified the problem for me. I
> > think we are leaning toward using the DTS on the 32 bit 2000 machine
> > and then replicating the data from 2000 to 2005. That brings up
> > another point..Am I correct in assuming that replication (probably
> > transactional) from SQL Server 2000 32 bit to SQL Server 2005 64 bit
> > would work? I would do a pull subscription from the 2005 box to the
> > 2000 box.- Hide quoted text -
> > - Show quoted text -- Hide quoted text -
> - Show quoted text -
I see your point but DTS is a lot more cumbersome than the new SSIS
and hard to keep updated. We move approx 20 gigs of data on a nightly
basis and then transform it into other relational databases. We don't
want to be forced to use the legacy DTS on the 64 bit 2005 SQL
edition. If we have to use the DTS we will keep it on an older 2000
box.|||Hi Kristina
"Kristina" wrote:
> On Mar 14, 5:52 pm, "Sylvain Lafontaine" <sylvain aei ca (fill the
> blanks, no spam please)> wrote:
> > You're right, there is no 64 bit Jet driver; so you cannot directly use an
> > ODBC connection to JET from a program running under the 64 bit mode.
> > However, to solve your problem, you have many other possibilities:
> >
> > 1- Run your DTS package from a second machine (could be a virtual machine),
> > preferably one with SQL-Server 2000 but you could also use an installation
> > of SQL-Server 2005 running in 32 bit mode and with the special add-in for
> > using DTS installed.
> >
> > Of course, instead of using a second machine or a virtual one, you could
> > also install a second instance of SQL-Server (either 2000 or 2005) but this
> > time running under the 32 bit mode.
> >
> > 2- Same thing than 1) with an installation of SQL-Server 2005 running in 32
> > bit mode but now with SSIS instead of DTS.
> >
> > 3- Use the 32 bit version of dtexec.exe (the one that you will find under
> > the "Program Files (x86)" folder branch). However, I don't know if you must
> > have an installation of SQL-Server running under 32 bit to do that or if you
> > can also execute DTS package.
> >
> > 4- Install SQL-Server 2005 Express and make your calls from SQL-Server 2005
> > 64 bit to the 32 bit ODBC driver through it, see:
> >
> > http://gorm-braarvig.blogspot.com/2005/11/access-database-from-sql-20...
> >
> > In your case, as you have already all the necessary DTS packages, I would
> > use them with an instance of SQL-Server running in 32 bit mode on one side
> > and SQL-Server 2005 64 bit on the other side as the target instead of
> > rewriting them with SSIS.
> >
> > --
> > Sylvain Lafontaine, ing.
> > MVP - Technologies Virtual-PC
> > E-mail: sylvain aei ca (fill the blanks, no spam please)
> >
> > "Kristina" <Kristina...@.gmail.com> wrote in message
> >
> > news:1173886263.138387.294260@.d57g2000hsg.googlegroups.com...
> >
> >
> >
> > > We are preparing to migrate all our 2000 databases to SQL Server 2005
> > > and have run into an unexpected issue. We pull in a lot of Legacy Data
> > > from a Thorougbred Basic main system. We use their 32 bit ODBC driver
> > > and they have not released a 64 bit driver. These jobs are done mostly
> > > through DTS and I was preparing to re-write all these existing
> > > programs in SSIS, but I just realized that the new 64 bit edidtion of
> > > SQL Server can't see the legacy 32 bit driver. Is there any work-
> > > around for this in SSIS? Most companies are releasing 64 bit
> > > compatable drivers (DB2, JODBC etc). I have tried entering the keys in
> > > the registry etc. to no avail. I need to create a SYSTEM DSN.
> >
> > > Again, we have to use ODBC.
> >
> > > Thanks in advance,
> > > Kristina- Hide quoted text -
> >
> > - Show quoted text -
>
> thanks for all the good advice. You clarified the problem for me. I
> think we are leaning toward using the DTS on the 32 bit 2000 machine
> and then replicating the data from 2000 to 2005. That brings up
> another point..Am I correct in assuming that replication (probably
> transactional) from SQL Server 2000 32 bit to SQL Server 2005 64 bit
> would work? I would do a pull subscription from the 2005 box to the
> 2000 box.
>
I would not expect you do need to use replication, as you can define an ODBC
datasource on your 32 bit instance that connects to your SQL 2005 server.
John|||Even if you use the 2000 DTS or the 2005 SSIS, you don't have to first ship
the data to SQL-Server 2000 or 2005 running in 32 bit mode, you can send
them directly to the SQL-Server 2005 running in 64 bit mode using either DTS
or SSIS 32 bit. The only obligation is that the program making the
connection with the 32 bit ODBC driver must be running itself in the 32 bit
mode but that doesn't forbid it to communicate directly with the SQL-Server
2005 64 bit at the other side of the transformation.
Also, even if you are running DTS on a SQL-Server 2000 or SSIS on a 32 bit
version of SQL-Server 2005, you don't have to send or retrieve data from one
of these two servers; are they are used only as the host for running the DTS
or the SSIS programs in 32 bit mode.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Kristina" <KristinaDBA@.gmail.com> wrote in message
news:1173967890.365144.154580@.l75g2000hse.googlegroups.com...
> On Mar 15, 10:02 am, "raibeart" <raibe...@.gmail.com> wrote:
>> Can't DTS connect to 64-bit SQL 2005?
>> Why couldn't you just move the data directly to 2005,
>> just use the 2000 DTS?
>>
>>
>> > > - Show quoted text -
>> > thanks for all the good advice. You clarified the problem for me. I
>> > think we are leaning toward using the DTS on the 32 bit 2000 machine
>> > and then replicating the data from 2000 to 2005. That brings up
>> > another point..Am I correct in assuming that replication (probably
>> > transactional) from SQL Server 2000 32 bit to SQL Server 2005 64 bit
>> > would work? I would do a pull subscription from the 2005 box to the
>> > 2000 box.- Hide quoted text -
>> > - Show quoted text -- Hide quoted text -
>> - Show quoted text -
> I see your point but DTS is a lot more cumbersome than the new SSIS
> and hard to keep updated. We move approx 20 gigs of data on a nightly
> basis and then transform it into other relational databases. We don't
> want to be forced to use the legacy DTS on the 64 bit 2005 SQL
> edition. If we have to use the DTS we will keep it on an older 2000
> box.
>|||On Mar 15, 10:20 am, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi Kristina
>
>
> "Kristina" wrote:
> > On Mar 14, 5:52 pm, "Sylvain Lafontaine" <sylvain aei ca (fill the
> > blanks, no spam please)> wrote:
> > > You're right, there is no 64 bit Jet driver; so you cannot directly use an
> > > ODBC connection to JET from a program running under the 64 bit mode.
> > > However, to solve your problem, you have many other possibilities:
> > > 1- Run your DTS package from a second machine (could be a virtual machine),
> > > preferably one with SQL-Server 2000 but you could also use an installation
> > > of SQL-Server 2005 running in 32 bit mode and with the special add-in for
> > > using DTS installed.
> > > Of course, instead of using a second machine or a virtual one, you could
> > > also install a second instance of SQL-Server (either 2000 or 2005) but this
> > > time running under the 32 bit mode.
> > > 2- Same thing than 1) with an installation of SQL-Server 2005 running in 32
> > > bit mode but now with SSIS instead of DTS.
> > > 3- Use the 32 bit version of dtexec.exe (the one that you will find under
> > > the "Program Files (x86)" folder branch). However, I don't know if you must
> > > have an installation of SQL-Server running under 32 bit to do that or if you
> > > can also execute DTS package.
> > > 4- Install SQL-Server 2005 Express and make your calls from SQL-Server 2005
> > > 64 bit to the 32 bit ODBC driver through it, see:
> > >http://gorm-braarvig.blogspot.com/2005/11/access-database-from-sql-20...
> > > In your case, as you have already all the necessary DTS packages, I would
> > > use them with an instance of SQL-Server running in 32 bit mode on one side
> > > and SQL-Server 2005 64 bit on the other side as the target instead of
> > > rewriting them with SSIS.
> > > --
> > > Sylvain Lafontaine, ing.
> > > MVP - Technologies Virtual-PC
> > > E-mail: sylvain aei ca (fill the blanks, no spam please)
> > > "Kristina" <Kristina...@.gmail.com> wrote in message
> > >news:1173886263.138387.294260@.d57g2000hsg.googlegroups.com...
> > > > We are preparing to migrate all our 2000 databases to SQL Server 2005
> > > > and have run into an unexpected issue. We pull in a lot of Legacy Data
> > > > from a Thorougbred Basic main system. We use their 32 bit ODBC driver
> > > > and they have not released a 64 bit driver. These jobs are done mostly
> > > > through DTS and I was preparing to re-write all these existing
> > > > programs in SSIS, but I just realized that the new 64 bit edidtion of
> > > > SQL Server can't see the legacy 32 bit driver. Is there any work-
> > > > around for this in SSIS? Most companies are releasing 64 bit
> > > > compatable drivers (DB2, JODBC etc). I have tried entering the keys in
> > > > the registry etc. to no avail. I need to create a SYSTEM DSN.
> > > > Again, we have to use ODBC.
> > > > Thanks in advance,
> > > > Kristina- Hide quoted text -
> > > - Show quoted text -
> > thanks for all the good advice. You clarified the problem for me. I
> > think we are leaning toward using the DTS on the 32 bit 2000 machine
> > and then replicating the data from 2000 to 2005. That brings up
> > another point..Am I correct in assuming that replication (probably
> > transactional) from SQL Server 2000 32 bit to SQL Server 2005 64 bit
> > would work? I would do a pull subscription from the 2005 box to the
> > 2000 box.
> I would not expect you do need to use replication, as you can define an ODBC
> datasource on your 32 bit instance that connects to your SQL 2005 server.
> John- Hide quoted text -
> - Show quoted text -
You guys are right. Thanks for the help!
Talk to you later with my next crisis moment.... :)|||On Mar 15, 12:59 pm, "Sylvain Lafontaine" <sylvain aei ca (fill the
blanks, no spam please)> wrote:
> Even if you use the 2000 DTS or the 2005 SSIS, you don't have to first ship
> the data to SQL-Server 2000 or 2005 running in 32 bit mode, you can send
> them directly to the SQL-Server 2005 running in 64 bit mode using either DTS
> or SSIS 32 bit. The only obligation is that the program making the
> connection with the 32 bit ODBC driver must be running itself in the 32 bit
> mode but that doesn't forbid it to communicate directly with the SQL-Server
> 2005 64 bit at the other side of the transformation.
> Also, even if you are running DTS on a SQL-Server 2000 or SSIS on a 32 bit
> version of SQL-Server 2005, you don't have to send or retrieve data from one
> of these two servers; are they are used only as the host for running the DTS
> or the SSIS programs in 32 bit mode.
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
> "Kristina" <Kristina...@.gmail.com> wrote in message
> news:1173967890.365144.154580@.l75g2000hse.googlegroups.com...
>
> > On Mar 15, 10:02 am, "raibeart" <raibe...@.gmail.com> wrote:
> >> Can't DTS connect to 64-bit SQL 2005?
> >> Why couldn't you just move the data directly to 2005,
> >> just use the 2000 DTS?
> >> > > - Show quoted text -
> >> > thanks for all the good advice. You clarified the problem for me. I
> >> > think we are leaning toward using the DTS on the 32 bit 2000 machine
> >> > and then replicating the data from 2000 to 2005. That brings up
> >> > another point..Am I correct in assuming that replication (probably
> >> > transactional) from SQL Server 2000 32 bit to SQL Server 2005 64 bit
> >> > would work? I would do a pull subscription from the 2005 box to the
> >> > 2000 box.- Hide quoted text -
> >> > - Show quoted text -- Hide quoted text -
> >> - Show quoted text -
> > I see your point but DTS is a lot more cumbersome than the new SSIS
> > and hard to keep updated. We move approx 20 gigs of data on a nightly
> > basis and then transform it into other relational databases. We don't
> > want to be forced to use the legacy DTS on the 64 bit 2005 SQL
> > edition. If we have to use the DTS we will keep it on an older 2000
> > box.- Hide quoted text -
> - Show quoted text -
You guys are right. Thanks for the help!
Talk to you later with my next crisis moment.... :)
Subscribe to:
Posts (Atom)