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

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

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

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!:confused: 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

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

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.:confused::

Have you tried using DTS to do the transfer?

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?????:confused:

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

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.)

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.

:)

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


I agree with you my friend :P)

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?