I am trying to run query to another

[riska]

I am trying to run query to another database via linked servers.

Now, I am testing a script on the two databases on my machine.
(The linked server is localhost)

If I run query:

select * from openquery( data_source, 'select * from pubs') it goes.

But if I run query in transaction :

begin tran
(select * from openquery( data_source, 'select * from pubs')
commit tran

it raises error:
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
Line 1: The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.

thank you for help

joseph

 

[tlbroadbent]

Certain restrictions apply to distributed transactions. SQL BOL contains details. Start with the following page.

http://msdn.microsoft.com/library/en-us/acdata/ac_8_qd_12_9ooj.asp

Why are you using a transaction for the select statement? Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[riska]

I need to migrate data from one database to other.
The whole migration is based on one entity with its relations.

The data copying must be enclosed in transaction because if I find the wrong data in related tables I'll do rollback and no data will be inserted into my base database.