DTS from 2005 to 2000

[Pauldohert]

I have DTS to pull data from a 2005 SQL db to my 2000 sql db.

What do I have to do to enalble transactions. I have to allow DTC to be running and allow network access if this was 2000 to 2000. Is there more to do since the source is 2005?

Thanks

 

[boblarson]

You will probably need to connect to it via an ODBC connection using the SQL Native Client driver.

 

[SQL_Hell]

Do you have a linked servers set up between these servers?

 

[Pauldohert]

I don't have likned servers set up - is that what I need? I don't seem to need that from a 2000 db to 2000db? Would I still use DTS if I set up linked servers.


Any help would be appreciated - I have done lots of reading , but maybe I am heading in the wrong direction! The server I am trying to connect to (I can using DTS without transactions) is that of a web site, I just have an IP and logon details for the db?

Thanks

 

[SQL_Hell]

Nope you dont really need a linked server to do the DTS transfer, but it will allow you a different method of achieving what you need, if you had a linked server then you could do the following in QA / Management studio

SELECT * INTO SERVER1.DB1.DBO.TABLE1 FROM SERVER2.DB1.DBO.TABLE1

If you dont really like this method or are not allowed to create linked server, then I think you need to install the 2005 SQL native client and backwards compatability on the SQL2000 box and that should hopefully fix your DTS problems.

Unfortunately I am currently working in a place that only has sql2000 boxes so I cant double check this for you

 

[Pauldohert]

I have no preference really - I suppose linked servers could be the answer. If it allows the transactions to work.

I am quite happy with the visual design capabilities that DTS offers me - ie the workflow, the simple way I can define the tasks I need to achieve - would this be lost if I used linked servers?

What is the advantage of one compared to the other?

I am very confused.:

 

[SQL_Hell]

Have you tried using DTS to do the transfer?

 

[Pauldohert]

Have you tried using DTS to do the transfer?

Eh? Uh? I am more lost than ever - I am using DTS It works fine - except I can't get it configured to use transactions?

Have you replied to the correct thread?????

 

[SQL_Hell]

Yes I replied in correct thread, just didn't know whether you'd actually tried the DTS and come accross an error or not.

I thought that if you transfer tables in DTS then they are bulk copied over so transactions shouldn't be an issue

 

[Pauldohert]

I have a few differnat tables to move records from - ie records that haven't previously been moved from the source to the destination db before. So first thing I am doing it marking the records in the source table - say Status = "Moving" - then when I have finished - I go back and re mark them "Moved".

I have realted tables to move also - so need to move records from them which have the PK table record marked "Moviing".

I am using transactions to try and make sure I take the PK records and all related records in one successful DTS package execution, or alternatively - roll back the transcation and take none. Its the only way I know that maybe how to do it.

If there is another, or better way I am happy to do that also??

Cheers for your help.

(error is "Unable to enlist in the transaction" - supposedly as in my fisrt post DTC is running and allow network access is enalbed, though I can't check this myself as its a db owned by another organistion, who asure me they are set correctly.)

 

[SQL_Hell]

Hi again,

I am a bit stumped on this one

The only possiblities are:

DTC is not running on one of the machines
DTC is configured properly on one of the machines

Or any of the following articles being relevant:

http://support.microsoft.com/kb/279857

http://www.sqlservercentral.com/Forums/Topic188460-19-1.aspx

http://support.microsoft.com/kb/914375

If you cannot get it working, then either do not use transactions and do the error handling another way, or use queries with linked servers to transfer the data. I suppose you could also consider doing a backup and restore and then remove the tables you dont need. Sorry I cant be of any more help.

 

[tooper12]

You will probably need to connect to it via an ODBC connection using the SQL Native Client driver.

I agree with you my friend )

 

[Pauldohert]

I should have followed up Bobs reply a bit, but I didn't understand it.

What exactly do I need to do - have you a link that an idiot can follow - does this allow DTS to work? I can't beleive importing data from one sqls server db from another sql server db is so difficult?