BOL says "The guest user can be deleted and added to all databases
except master and tempdb, where it must always exist."
any reason for guest to be in msdb?I usually remove guest from msdb, but to be more certain before posting this I asked around my MVP
and MCT colleagues. No one has experienced any bad effect from removing guest from msdb. Of course,
for a non-sysadmin to create jobs etc, you need to add that login to msdb, but the fact that you now
can control who can do what is a good thing, IMO (and might just be why you posted this in the first
place :-) ).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ch" <ch@.dontemailme.com> wrote in message news:41862F84.647BCB04@.dontemailme.com...
> BOL says "The guest user can be deleted and added to all databases
> except master and tempdb, where it must always exist."
> any reason for guest to be in msdb?
Showing posts with label databases. Show all posts
Showing posts with label databases. Show all posts
Wednesday, March 28, 2012
OK to delete tranlog backups?
Hi. bit of a newbie question. My predecessor set up maintenance plans in
SQL 2000 to back up databases and tranlogs (two separate plans.) The
database backup runs nightly. The tranlog backups run on the half-hour
during busines hours. In the folder where the backups are stored, there are
many tranlog backup filess. They are not being purged as designed in the
maint plan (there is a statement in the job step that says delete after 2
days.) Two questions: why are the tranlog backup files not being purged as
expected, and how do I know which log backup files I can delete manaually?
Thanks.Coop wrote:
> Hi. bit of a newbie question. My predecessor set up maintenance plans in
> SQL 2000 to back up databases and tranlogs (two separate plans.) The
> database backup runs nightly. The tranlog backups run on the half-hour
> during busines hours. In the folder where the backups are stored, there are
> many tranlog backup filess. They are not being purged as designed in the
> maint plan (there is a statement in the job step that says delete after 2
> days.) Two questions: why are the tranlog backup files not being purged as
> expected, and how do I know which log backup files I can delete manaually?
Q1: if the logs aren't named with the same rules as the maintplan, then
the agent won't consider them to be 'old'
Q2: as long as you don't need to playback between full backups (or if
you have copies on tape or elsewhere), you should be able to safely
delete any tranlog bkup files that preceed the timestamp for your latest
full backup file.|||Q1: Also, this can happen if the log backup plan includes databases in simple recovery model.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"horseradish" <millardjk@.gmail.com> wrote in message news:uCD1wKYqHHA.3484@.TK2MSFTNGP05.phx.gbl...
> Coop wrote:
>> Hi. bit of a newbie question. My predecessor set up maintenance plans in
>> SQL 2000 to back up databases and tranlogs (two separate plans.) The
>> database backup runs nightly. The tranlog backups run on the half-hour
>> during busines hours. In the folder where the backups are stored, there are
>> many tranlog backup filess. They are not being purged as designed in the
>> maint plan (there is a statement in the job step that says delete after 2
>> days.) Two questions: why are the tranlog backup files not being purged as
>> expected, and how do I know which log backup files I can delete manaually?
> Q1: if the logs aren't named with the same rules as the maintplan, then
> the agent won't consider them to be 'old'
> Q2: as long as you don't need to playback between full backups (or if
> you have copies on tape or elsewhere), you should be able to safely
> delete any tranlog bkup files that preceed the timestamp for your latest
> full backup file.
SQL 2000 to back up databases and tranlogs (two separate plans.) The
database backup runs nightly. The tranlog backups run on the half-hour
during busines hours. In the folder where the backups are stored, there are
many tranlog backup filess. They are not being purged as designed in the
maint plan (there is a statement in the job step that says delete after 2
days.) Two questions: why are the tranlog backup files not being purged as
expected, and how do I know which log backup files I can delete manaually?
Thanks.Coop wrote:
> Hi. bit of a newbie question. My predecessor set up maintenance plans in
> SQL 2000 to back up databases and tranlogs (two separate plans.) The
> database backup runs nightly. The tranlog backups run on the half-hour
> during busines hours. In the folder where the backups are stored, there are
> many tranlog backup filess. They are not being purged as designed in the
> maint plan (there is a statement in the job step that says delete after 2
> days.) Two questions: why are the tranlog backup files not being purged as
> expected, and how do I know which log backup files I can delete manaually?
Q1: if the logs aren't named with the same rules as the maintplan, then
the agent won't consider them to be 'old'
Q2: as long as you don't need to playback between full backups (or if
you have copies on tape or elsewhere), you should be able to safely
delete any tranlog bkup files that preceed the timestamp for your latest
full backup file.|||Q1: Also, this can happen if the log backup plan includes databases in simple recovery model.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"horseradish" <millardjk@.gmail.com> wrote in message news:uCD1wKYqHHA.3484@.TK2MSFTNGP05.phx.gbl...
> Coop wrote:
>> Hi. bit of a newbie question. My predecessor set up maintenance plans in
>> SQL 2000 to back up databases and tranlogs (two separate plans.) The
>> database backup runs nightly. The tranlog backups run on the half-hour
>> during busines hours. In the folder where the backups are stored, there are
>> many tranlog backup filess. They are not being purged as designed in the
>> maint plan (there is a statement in the job step that says delete after 2
>> days.) Two questions: why are the tranlog backup files not being purged as
>> expected, and how do I know which log backup files I can delete manaually?
> Q1: if the logs aren't named with the same rules as the maintplan, then
> the agent won't consider them to be 'old'
> Q2: as long as you don't need to playback between full backups (or if
> you have copies on tape or elsewhere), you should be able to safely
> delete any tranlog bkup files that preceed the timestamp for your latest
> full backup file.
OK to delete tranlog backups?
Hi. bit of a newbie question. My predecessor set up maintenance plans in
SQL 2000 to back up databases and tranlogs (two separate plans.) The
database backup runs nightly. The tranlog backups run on the half-hour
during busines hours. In the folder where the backups are stored, there are
many tranlog backup filess. They are not being purged as designed in the
maint plan (there is a statement in the job step that says delete after 2
days.) Two questions: why are the tranlog backup files not being purged as
expected, and how do I know which log backup files I can delete manaually?
Thanks.
Coop wrote:
> Hi. bit of a newbie question. My predecessor set up maintenance plans in
> SQL 2000 to back up databases and tranlogs (two separate plans.) The
> database backup runs nightly. The tranlog backups run on the half-hour
> during busines hours. In the folder where the backups are stored, there are
> many tranlog backup filess. They are not being purged as designed in the
> maint plan (there is a statement in the job step that says delete after 2
> days.) Two questions: why are the tranlog backup files not being purged as
> expected, and how do I know which log backup files I can delete manaually?
Q1: if the logs aren't named with the same rules as the maintplan, then
the agent won't consider them to be 'old'
Q2: as long as you don't need to playback between full backups (or if
you have copies on tape or elsewhere), you should be able to safely
delete any tranlog bkup files that preceed the timestamp for your latest
full backup file.
|||Q1: Also, this can happen if the log backup plan includes databases in simple recovery model.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"horseradish" <millardjk@.gmail.com> wrote in message news:uCD1wKYqHHA.3484@.TK2MSFTNGP05.phx.gbl...
> Coop wrote:
> Q1: if the logs aren't named with the same rules as the maintplan, then
> the agent won't consider them to be 'old'
> Q2: as long as you don't need to playback between full backups (or if
> you have copies on tape or elsewhere), you should be able to safely
> delete any tranlog bkup files that preceed the timestamp for your latest
> full backup file.
SQL 2000 to back up databases and tranlogs (two separate plans.) The
database backup runs nightly. The tranlog backups run on the half-hour
during busines hours. In the folder where the backups are stored, there are
many tranlog backup filess. They are not being purged as designed in the
maint plan (there is a statement in the job step that says delete after 2
days.) Two questions: why are the tranlog backup files not being purged as
expected, and how do I know which log backup files I can delete manaually?
Thanks.
Coop wrote:
> Hi. bit of a newbie question. My predecessor set up maintenance plans in
> SQL 2000 to back up databases and tranlogs (two separate plans.) The
> database backup runs nightly. The tranlog backups run on the half-hour
> during busines hours. In the folder where the backups are stored, there are
> many tranlog backup filess. They are not being purged as designed in the
> maint plan (there is a statement in the job step that says delete after 2
> days.) Two questions: why are the tranlog backup files not being purged as
> expected, and how do I know which log backup files I can delete manaually?
Q1: if the logs aren't named with the same rules as the maintplan, then
the agent won't consider them to be 'old'
Q2: as long as you don't need to playback between full backups (or if
you have copies on tape or elsewhere), you should be able to safely
delete any tranlog bkup files that preceed the timestamp for your latest
full backup file.
|||Q1: Also, this can happen if the log backup plan includes databases in simple recovery model.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"horseradish" <millardjk@.gmail.com> wrote in message news:uCD1wKYqHHA.3484@.TK2MSFTNGP05.phx.gbl...
> Coop wrote:
> Q1: if the logs aren't named with the same rules as the maintplan, then
> the agent won't consider them to be 'old'
> Q2: as long as you don't need to playback between full backups (or if
> you have copies on tape or elsewhere), you should be able to safely
> delete any tranlog bkup files that preceed the timestamp for your latest
> full backup file.
OK to delete tranlog backups?
Hi. bit of a newbie question. My predecessor set up maintenance plans in
SQL 2000 to back up databases and tranlogs (two separate plans.) The
database backup runs nightly. The tranlog backups run on the half-hour
during busines hours. In the folder where the backups are stored, there are
many tranlog backup filess. They are not being purged as designed in the
maint plan (there is a statement in the job step that says delete after 2
days.) Two questions: why are the tranlog backup files not being purged as
expected, and how do I know which log backup files I can delete manaually?
Thanks.Coop wrote:
> Hi. bit of a newbie question. My predecessor set up maintenance plans in
> SQL 2000 to back up databases and tranlogs (two separate plans.) The
> database backup runs nightly. The tranlog backups run on the half-hour
> during busines hours. In the folder where the backups are stored, there a
re
> many tranlog backup filess. They are not being purged as designed in the
> maint plan (there is a statement in the job step that says delete after 2
> days.) Two questions: why are the tranlog backup files not being purged
as
> expected, and how do I know which log backup files I can delete manaually?
Q1: if the logs aren't named with the same rules as the maintplan, then
the agent won't consider them to be 'old'
Q2: as long as you don't need to playback between full backups (or if
you have copies on tape or elsewhere), you should be able to safely
delete any tranlog bkup files that preceed the timestamp for your latest
full backup file.|||Q1: Also, this can happen if the log backup plan includes databases in simpl
e recovery model.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"horseradish" <millardjk@.gmail.com> wrote in message news:uCD1wKYqHHA.3484@.TK2MSFTNGP05.phx.
gbl...
> Coop wrote:
> Q1: if the logs aren't named with the same rules as the maintplan, then
> the agent won't consider them to be 'old'
> Q2: as long as you don't need to playback between full backups (or if
> you have copies on tape or elsewhere), you should be able to safely
> delete any tranlog bkup files that preceed the timestamp for your latest
> full backup file.sql
SQL 2000 to back up databases and tranlogs (two separate plans.) The
database backup runs nightly. The tranlog backups run on the half-hour
during busines hours. In the folder where the backups are stored, there are
many tranlog backup filess. They are not being purged as designed in the
maint plan (there is a statement in the job step that says delete after 2
days.) Two questions: why are the tranlog backup files not being purged as
expected, and how do I know which log backup files I can delete manaually?
Thanks.Coop wrote:
> Hi. bit of a newbie question. My predecessor set up maintenance plans in
> SQL 2000 to back up databases and tranlogs (two separate plans.) The
> database backup runs nightly. The tranlog backups run on the half-hour
> during busines hours. In the folder where the backups are stored, there a
re
> many tranlog backup filess. They are not being purged as designed in the
> maint plan (there is a statement in the job step that says delete after 2
> days.) Two questions: why are the tranlog backup files not being purged
as
> expected, and how do I know which log backup files I can delete manaually?
Q1: if the logs aren't named with the same rules as the maintplan, then
the agent won't consider them to be 'old'
Q2: as long as you don't need to playback between full backups (or if
you have copies on tape or elsewhere), you should be able to safely
delete any tranlog bkup files that preceed the timestamp for your latest
full backup file.|||Q1: Also, this can happen if the log backup plan includes databases in simpl
e recovery model.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"horseradish" <millardjk@.gmail.com> wrote in message news:uCD1wKYqHHA.3484@.TK2MSFTNGP05.phx.
gbl...
> Coop wrote:
> Q1: if the logs aren't named with the same rules as the maintplan, then
> the agent won't consider them to be 'old'
> Q2: as long as you don't need to playback between full backups (or if
> you have copies on tape or elsewhere), you should be able to safely
> delete any tranlog bkup files that preceed the timestamp for your latest
> full backup file.sql
Monday, March 26, 2012
Offtopic: "query analyzer" for odbc datasources
I usually do sql-server programming but sometimes I also need to test and query ODBC-datasources with SQL such as Access or Progress databases. I usually do this using active server pages which I find rather obscure, and I would prefer to have a nice little program sort of like query analyzer, where I can specify the connection-string and write my SQL in there somewhere. Does anyone know of such a program?To test and query ODBC-datasources in the manner described one may use Microsoft Query MSQRY32.exe, Sql Plus, Sql *, or a variety of similar tools from packages like ER Studio, ERWin, etc.|||I posted this message in a usenet group aswell and someone came up with a neet little _free_ program that was exactly what I was looking for with alot of nice features...
-> http://www.indus-soft.com/winsql/sql
-> http://www.indus-soft.com/winsql/sql
Offline data synchronisation
Does anyone know of a solution for OFFLINE Data Synchronisation for .Net
applications sitting on top of MS SQL databases in an asynchronous, limited
and expensive bandwidth (<=9600bps) configuration (i.e. synchronisation data
need to be exchanged in the form of files or any other asynchronous method.
Connections of up to 9600bps make establishing sql connection through tcp/ip
very costly)
I need to create about 50 DB's and keep certain data in synch with a central
DB. I thus need to (ideally) be able to “target” information to particular DB
when synchronising from the central DB out to the 50 instances. Data from
these databases should also be synchronised with my central DB. Conflict
resolution is another issue that should be dealt with.
I would consider anything which can deliver a flexible, operable and
reliable solution (off the shelf product, generic or specific, best practise,
design, sample, etc).
I have briefly looked into SQL Server 2005 Replication Services
(publication,subscription) and although it seems like a perfect solution I
could not figure out if it would work in the offline, asynchronous mode (i.e.
through file exchange and not direct sql connection).
Please point me in the right direction!
Thanks in advance…
Lastis,
replication won't work if you have no SQL connection. A few requests have
been posted for a file-based solution, so you might want to add it to the
SQL wish list.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||We might be able to help you here. Contact me offlist and I can tell you
more about our synchronization technology.
Andrew
"Lastis" <Lastis@.discussions.microsoft.com> wrote in message
news:3EF48BAF-4B96-4144-8C21-49E78EC81A20@.microsoft.com...
> Does anyone know of a solution for OFFLINE Data Synchronisation for .Net
> applications sitting on top of MS SQL databases in an asynchronous,
> limited
> and expensive bandwidth (<=9600bps) configuration (i.e. synchronisation
> data
> need to be exchanged in the form of files or any other asynchronous
> method.
> Connections of up to 9600bps make establishing sql connection through
> tcp/ip
> very costly)
> I need to create about 50 DB's and keep certain data in synch with a
> central
> DB. I thus need to (ideally) be able to "target" information to particular
> DB
> when synchronising from the central DB out to the 50 instances. Data from
> these databases should also be synchronised with my central DB. Conflict
> resolution is another issue that should be dealt with.
> I would consider anything which can deliver a flexible, operable and
> reliable solution (off the shelf product, generic or specific, best
> practise,
> design, sample, etc).
> I have briefly looked into SQL Server 2005 Replication Services
> (publication,subscription) and although it seems like a perfect solution I
> could not figure out if it would work in the offline, asynchronous mode
> (i.e.
> through file exchange and not direct sql connection).
> Please point me in the right direction!
> Thanks in advance.
>
>
|||How can I contact you? I tried using the email in your profile but it did
not work.
|||Remove the nospam part from my email address. Sorry, I need to change that.
"Lastis" <Lastis@.discussions.microsoft.com> wrote in message
news:64EED715-CEB0-4828-BA2E-8C8E95F74762@.microsoft.com...
> How can I contact you? I tried using the email in your profile but it did
> not work.
applications sitting on top of MS SQL databases in an asynchronous, limited
and expensive bandwidth (<=9600bps) configuration (i.e. synchronisation data
need to be exchanged in the form of files or any other asynchronous method.
Connections of up to 9600bps make establishing sql connection through tcp/ip
very costly)
I need to create about 50 DB's and keep certain data in synch with a central
DB. I thus need to (ideally) be able to “target” information to particular DB
when synchronising from the central DB out to the 50 instances. Data from
these databases should also be synchronised with my central DB. Conflict
resolution is another issue that should be dealt with.
I would consider anything which can deliver a flexible, operable and
reliable solution (off the shelf product, generic or specific, best practise,
design, sample, etc).
I have briefly looked into SQL Server 2005 Replication Services
(publication,subscription) and although it seems like a perfect solution I
could not figure out if it would work in the offline, asynchronous mode (i.e.
through file exchange and not direct sql connection).
Please point me in the right direction!
Thanks in advance…
Lastis,
replication won't work if you have no SQL connection. A few requests have
been posted for a file-based solution, so you might want to add it to the
SQL wish list.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||We might be able to help you here. Contact me offlist and I can tell you
more about our synchronization technology.
Andrew
"Lastis" <Lastis@.discussions.microsoft.com> wrote in message
news:3EF48BAF-4B96-4144-8C21-49E78EC81A20@.microsoft.com...
> Does anyone know of a solution for OFFLINE Data Synchronisation for .Net
> applications sitting on top of MS SQL databases in an asynchronous,
> limited
> and expensive bandwidth (<=9600bps) configuration (i.e. synchronisation
> data
> need to be exchanged in the form of files or any other asynchronous
> method.
> Connections of up to 9600bps make establishing sql connection through
> tcp/ip
> very costly)
> I need to create about 50 DB's and keep certain data in synch with a
> central
> DB. I thus need to (ideally) be able to "target" information to particular
> DB
> when synchronising from the central DB out to the 50 instances. Data from
> these databases should also be synchronised with my central DB. Conflict
> resolution is another issue that should be dealt with.
> I would consider anything which can deliver a flexible, operable and
> reliable solution (off the shelf product, generic or specific, best
> practise,
> design, sample, etc).
> I have briefly looked into SQL Server 2005 Replication Services
> (publication,subscription) and although it seems like a perfect solution I
> could not figure out if it would work in the offline, asynchronous mode
> (i.e.
> through file exchange and not direct sql connection).
> Please point me in the right direction!
> Thanks in advance.
>
>
|||How can I contact you? I tried using the email in your profile but it did
not work.
|||Remove the nospam part from my email address. Sorry, I need to change that.
"Lastis" <Lastis@.discussions.microsoft.com> wrote in message
news:64EED715-CEB0-4828-BA2E-8C8E95F74762@.microsoft.com...
> How can I contact you? I tried using the email in your profile but it did
> not work.
Friday, March 23, 2012
Offline
I just read that in the Microsoft SQL Server 2000
Resource Kit, it recommends not putting databases in
offline. I don't have that resource kit. Can someone
let me know the pros/cons as I have some databases that I
had considered doing this with.Hi,
Not sure why you are interested in putting the database offline. In case
you don't want the database to be accessed by other users you can put the
database in a single user mode instead.
Thanks,
Fargham
Resource Kit, it recommends not putting databases in
offline. I don't have that resource kit. Can someone
let me know the pros/cons as I have some databases that I
had considered doing this with.Hi,
Not sure why you are interested in putting the database offline. In case
you don't want the database to be accessed by other users you can put the
database in a single user mode instead.
Thanks,
Fargham
Offline
I just read that in the Microsoft SQL Server 2000
Resource Kit, it recommends not putting databases in
offline. I don't have that resource kit. Can someone
let me know the pros/cons as I have some databases that I
had considered doing this with.
Hi,
Not sure why you are interested in putting the database offline. In case
you don't want the database to be accessed by other users you can put the
database in a single user mode instead.
Thanks,
Fargham
Resource Kit, it recommends not putting databases in
offline. I don't have that resource kit. Can someone
let me know the pros/cons as I have some databases that I
had considered doing this with.
Hi,
Not sure why you are interested in putting the database offline. In case
you don't want the database to be accessed by other users you can put the
database in a single user mode instead.
Thanks,
Fargham
Monday, March 12, 2012
odbc, linux, and mssql
I'm not really experenced with databases or odbc but I have a need to connect to a mssql 2000 server for work. I have installed openlink odbc client on my Red Hat 7.3 box. The docs are very confusing and I'm really stumped on what I need here to connect. Any help here will keep me from pulling anymore of my hair out!
I hope I am providing enough information.
I'm using the the " OpenLink Generic ODBC Driver" and when I try to connect I get the following:
Failed
SQLDriverConnect on Client failed.
DB_Connect Error
[OpenLink][ODBC]RPC: Remote system error, SQLSTATE=08004
[OpenLink][ODBC]Connection rejected by data source, SQLSTATE=08004
my odbc.inc file looks like this:
; portfolio, please check out our web site at:
;
; http://www.openlinksw.com
;
; or contact us at:
;
; general.information@.openlinksw.com
;
; If you have any technical questions, please contact our support
; staff at:
;
; technical.support@.openlinksw.com
;
[ODBC Data Sources]
OpenLink = OpenLink Generic ODBC Driver
trackit = OpenLink Generic ODBC Driver
[Default]
Driver = /usr/openlink/lib/oplodbc.so.1
[OpenLink]
Driver = /usr/openlink/lib/oplodbc.so.1
ServerType = Oracle 8.1.x
Username =
Password =
Database =
Options =
FetchBufferSize = 99
ReadOnly = no
DeferLongFetch = no
Host = localhost
Protocol = TCP/IP
Description = Sample Oracle 8.1.x Connection
[trackit]
Description = Sample DSN
Options = trackit
Port = 5000
Host = trackit.premdor.com
UserName = JR
ServerType = SQLServer 2000
Protocol = TCP/IP
Driver = /usr/openlink/lib/oplodbc.so.1
Database = trackit
ReadOnly = Yes
NoLoginBox = No
FetchBufferSize = 99Hi,
I haven't tried using this "open link ODBC driver" yet but i guess you need to use the 'SQL Server ODBC driver for Linux".
hth!|||Your odbc.inc file does not include an entry for SQL server ....
I hope I am providing enough information.
I'm using the the " OpenLink Generic ODBC Driver" and when I try to connect I get the following:
Failed
SQLDriverConnect on Client failed.
DB_Connect Error
[OpenLink][ODBC]RPC: Remote system error, SQLSTATE=08004
[OpenLink][ODBC]Connection rejected by data source, SQLSTATE=08004
my odbc.inc file looks like this:
; portfolio, please check out our web site at:
;
; http://www.openlinksw.com
;
; or contact us at:
;
; general.information@.openlinksw.com
;
; If you have any technical questions, please contact our support
; staff at:
;
; technical.support@.openlinksw.com
;
[ODBC Data Sources]
OpenLink = OpenLink Generic ODBC Driver
trackit = OpenLink Generic ODBC Driver
[Default]
Driver = /usr/openlink/lib/oplodbc.so.1
[OpenLink]
Driver = /usr/openlink/lib/oplodbc.so.1
ServerType = Oracle 8.1.x
Username =
Password =
Database =
Options =
FetchBufferSize = 99
ReadOnly = no
DeferLongFetch = no
Host = localhost
Protocol = TCP/IP
Description = Sample Oracle 8.1.x Connection
[trackit]
Description = Sample DSN
Options = trackit
Port = 5000
Host = trackit.premdor.com
UserName = JR
ServerType = SQLServer 2000
Protocol = TCP/IP
Driver = /usr/openlink/lib/oplodbc.so.1
Database = trackit
ReadOnly = Yes
NoLoginBox = No
FetchBufferSize = 99Hi,
I haven't tried using this "open link ODBC driver" yet but i guess you need to use the 'SQL Server ODBC driver for Linux".
hth!|||Your odbc.inc file does not include an entry for SQL server ....
Saturday, February 25, 2012
ODBC problems
I have next to no experience with databases, so please bear with me. We have an application that requires setting up a system DSN with a SQL Server driver. I am able to do so on one computer. When I go to another I start the process to add the DSN, I type in the server name and then I have to select the default database on that server. In the dropdown list of databases, only 5 or 6 appear... none of which is the one that I need. I am going through the same steps on each computer and do not know why it would not be recognizing it. Does anyone have any ideas?By the way, I have SQL Server 2000 on Windows Server 2003. All machines are Windows XP with Windows firewall disabled.|||More than likely it is a permissions issue. You would have to grant access to the target database for the login that you are using.
In Enterprise Manager, expand the server node, expand the security node, click on the logins node. In the right-hand pane, select the login you are using and double click. On the database access tab, check the databases to which the login will have access. Also check the db_owner item in the right-hand pane**.
** Note that this is a BAD practice. However, you are indicating that you have no db experience and this will ensure that you get your users up and running quickly with minimal issues which good security can sometimes cause. You are essentially exposing your data to the user community and they may be able to update, delete data or even whole tables with the db_owner permission. Read up on user security and tighten the security back down when you understand better what your user requirements are.
Regards,
hmscott
In Enterprise Manager, expand the server node, expand the security node, click on the logins node. In the right-hand pane, select the login you are using and double click. On the database access tab, check the databases to which the login will have access. Also check the db_owner item in the right-hand pane**.
** Note that this is a BAD practice. However, you are indicating that you have no db experience and this will ensure that you get your users up and running quickly with minimal issues which good security can sometimes cause. You are essentially exposing your data to the user community and they may be able to update, delete data or even whole tables with the db_owner permission. Read up on user security and tighten the security back down when you understand better what your user requirements are.
Regards,
hmscott
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.... :)
Monday, February 20, 2012
odbc link failed
Hello,
I have a windows SBS2003 server with msde installed.
I created some odbc datalinks in microsoft acces to one of my sql databases.
The links are ok end i can access the odbc links in microsoft acces on my
server.
Problem :
When i open the access database on my workstation and try to connect to one
of the odbc links, i getr the followin error.
ODBC-connection to 'SQL Server<servername> failed
Microsoft Access can't open the table in Datasheet View
Any suggestions?
Thank you,
Hi
Your MSDE instance was probably installed without the network protocols
See http://support.microsoft.com/?kbid=814130 on how to run svrnetcn.exe.
John
"Ponch" wrote:
> Hello,
> I have a windows SBS2003 server with msde installed.
> I created some odbc datalinks in microsoft acces to one of my sql databases.
> The links are ok end i can access the odbc links in microsoft acces on my
> server.
> Problem :
> When i open the access database on my workstation and try to connect to one
> of the odbc links, i getr the followin error.
> ODBC-connection to 'SQL Server<servername> failed
> Microsoft Access can't open the table in Datasheet View
> Any suggestions?
> Thank you,
>
>
|||Hello, Thanks for the tip, it solved my problem.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:4FD2882D-37A0-4EE7-B7A2-1C8BDEF50908@.microsoft.com...[vbcol=seagreen]
> Hi
> Your MSDE instance was probably installed without the network protocols
> See http://support.microsoft.com/?kbid=814130 on how to run svrnetcn.exe.
> John
> "Ponch" wrote:
I have a windows SBS2003 server with msde installed.
I created some odbc datalinks in microsoft acces to one of my sql databases.
The links are ok end i can access the odbc links in microsoft acces on my
server.
Problem :
When i open the access database on my workstation and try to connect to one
of the odbc links, i getr the followin error.
ODBC-connection to 'SQL Server<servername> failed
Microsoft Access can't open the table in Datasheet View
Any suggestions?
Thank you,
Hi
Your MSDE instance was probably installed without the network protocols
See http://support.microsoft.com/?kbid=814130 on how to run svrnetcn.exe.
John
"Ponch" wrote:
> Hello,
> I have a windows SBS2003 server with msde installed.
> I created some odbc datalinks in microsoft acces to one of my sql databases.
> The links are ok end i can access the odbc links in microsoft acces on my
> server.
> Problem :
> When i open the access database on my workstation and try to connect to one
> of the odbc links, i getr the followin error.
> ODBC-connection to 'SQL Server<servername> failed
> Microsoft Access can't open the table in Datasheet View
> Any suggestions?
> Thank you,
>
>
|||Hello, Thanks for the tip, it solved my problem.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:4FD2882D-37A0-4EE7-B7A2-1C8BDEF50908@.microsoft.com...[vbcol=seagreen]
> Hi
> Your MSDE instance was probably installed without the network protocols
> See http://support.microsoft.com/?kbid=814130 on how to run svrnetcn.exe.
> John
> "Ponch" wrote:
odbc link failed
Hello,
I have a windows SBS2003 server with msde installed.
I created some odbc datalinks in microsoft acces to one of my sql databases.
The links are ok end i can access the odbc links in microsoft acces on my
server.
Problem :
When i open the access database on my workstation and try to connect to one
of the odbc links, i getr the followin error.
ODBC-connection to 'SQL Server<servername> failed
Microsoft Access can't open the table in Datasheet View
Any suggestions?
Thank you,Hi
Your MSDE instance was probably installed without the network protocols
See http://support.microsoft.com/?kbid=814130 on how to run svrnetcn.exe.
John
"Ponch" wrote:
> Hello,
> I have a windows SBS2003 server with msde installed.
> I created some odbc datalinks in microsoft acces to one of my sql database
s.
> The links are ok end i can access the odbc links in microsoft acces on my
> server.
> Problem :
> When i open the access database on my workstation and try to connect to on
e
> of the odbc links, i getr the followin error.
> ODBC-connection to 'SQL Server<servername> failed
> Microsoft Access can't open the table in Datasheet View
> Any suggestions?
> Thank you,
>
>|||Hello, Thanks for the tip, it solved my problem.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:4FD2882D-37A0-4EE7-B7A2-1C8BDEF50908@.microsoft.com...[vbcol=seagreen]
> Hi
> Your MSDE instance was probably installed without the network protocols
> See http://support.microsoft.com/?kbid=814130 on how to run svrnetcn.exe.
> John
> "Ponch" wrote:
>
I have a windows SBS2003 server with msde installed.
I created some odbc datalinks in microsoft acces to one of my sql databases.
The links are ok end i can access the odbc links in microsoft acces on my
server.
Problem :
When i open the access database on my workstation and try to connect to one
of the odbc links, i getr the followin error.
ODBC-connection to 'SQL Server<servername> failed
Microsoft Access can't open the table in Datasheet View
Any suggestions?
Thank you,Hi
Your MSDE instance was probably installed without the network protocols
See http://support.microsoft.com/?kbid=814130 on how to run svrnetcn.exe.
John
"Ponch" wrote:
> Hello,
> I have a windows SBS2003 server with msde installed.
> I created some odbc datalinks in microsoft acces to one of my sql database
s.
> The links are ok end i can access the odbc links in microsoft acces on my
> server.
> Problem :
> When i open the access database on my workstation and try to connect to on
e
> of the odbc links, i getr the followin error.
> ODBC-connection to 'SQL Server<servername> failed
> Microsoft Access can't open the table in Datasheet View
> Any suggestions?
> Thank you,
>
>|||Hello, Thanks for the tip, it solved my problem.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:4FD2882D-37A0-4EE7-B7A2-1C8BDEF50908@.microsoft.com...[vbcol=seagreen]
> Hi
> Your MSDE instance was probably installed without the network protocols
> See http://support.microsoft.com/?kbid=814130 on how to run svrnetcn.exe.
> John
> "Ponch" wrote:
>
odbc link failed
Hello,
I have a windows SBS2003 server with msde installed.
I created some odbc datalinks in microsoft acces to one of my sql databases.
The links are ok end i can access the odbc links in microsoft acces on my
server.
Problem :
When i open the access database on my workstation and try to connect to one
of the odbc links, i getr the followin error.
ODBC-connection to 'SQL Server<servername> failed
Microsoft Access can't open the table in Datasheet View
Any suggestions?
Thank you,Hi
Your MSDE instance was probably installed without the network protocols
See http://support.microsoft.com/?kbid=814130 on how to run svrnetcn.exe.
John
"Ponch" wrote:
> Hello,
> I have a windows SBS2003 server with msde installed.
> I created some odbc datalinks in microsoft acces to one of my sql databases.
> The links are ok end i can access the odbc links in microsoft acces on my
> server.
> Problem :
> When i open the access database on my workstation and try to connect to one
> of the odbc links, i getr the followin error.
> ODBC-connection to 'SQL Server<servername> failed
> Microsoft Access can't open the table in Datasheet View
> Any suggestions?
> Thank you,
>
>|||Hello, Thanks for the tip, it solved my problem.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:4FD2882D-37A0-4EE7-B7A2-1C8BDEF50908@.microsoft.com...
> Hi
> Your MSDE instance was probably installed without the network protocols
> See http://support.microsoft.com/?kbid=814130 on how to run svrnetcn.exe.
> John
> "Ponch" wrote:
>> Hello,
>> I have a windows SBS2003 server with msde installed.
>> I created some odbc datalinks in microsoft acces to one of my sql
>> databases.
>> The links are ok end i can access the odbc links in microsoft acces on my
>> server.
>> Problem :
>> When i open the access database on my workstation and try to connect to
>> one
>> of the odbc links, i getr the followin error.
>> ODBC-connection to 'SQL Server<servername> failed
>> Microsoft Access can't open the table in Datasheet View
>> Any suggestions?
>> Thank you,
>>
>>
I have a windows SBS2003 server with msde installed.
I created some odbc datalinks in microsoft acces to one of my sql databases.
The links are ok end i can access the odbc links in microsoft acces on my
server.
Problem :
When i open the access database on my workstation and try to connect to one
of the odbc links, i getr the followin error.
ODBC-connection to 'SQL Server<servername> failed
Microsoft Access can't open the table in Datasheet View
Any suggestions?
Thank you,Hi
Your MSDE instance was probably installed without the network protocols
See http://support.microsoft.com/?kbid=814130 on how to run svrnetcn.exe.
John
"Ponch" wrote:
> Hello,
> I have a windows SBS2003 server with msde installed.
> I created some odbc datalinks in microsoft acces to one of my sql databases.
> The links are ok end i can access the odbc links in microsoft acces on my
> server.
> Problem :
> When i open the access database on my workstation and try to connect to one
> of the odbc links, i getr the followin error.
> ODBC-connection to 'SQL Server<servername> failed
> Microsoft Access can't open the table in Datasheet View
> Any suggestions?
> Thank you,
>
>|||Hello, Thanks for the tip, it solved my problem.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:4FD2882D-37A0-4EE7-B7A2-1C8BDEF50908@.microsoft.com...
> Hi
> Your MSDE instance was probably installed without the network protocols
> See http://support.microsoft.com/?kbid=814130 on how to run svrnetcn.exe.
> John
> "Ponch" wrote:
>> Hello,
>> I have a windows SBS2003 server with msde installed.
>> I created some odbc datalinks in microsoft acces to one of my sql
>> databases.
>> The links are ok end i can access the odbc links in microsoft acces on my
>> server.
>> Problem :
>> When i open the access database on my workstation and try to connect to
>> one
>> of the odbc links, i getr the followin error.
>> ODBC-connection to 'SQL Server<servername> failed
>> Microsoft Access can't open the table in Datasheet View
>> Any suggestions?
>> Thank you,
>>
>>
Subscribe to:
Posts (Atom)