insert data between SQL servers
 

I have production data on one server and test data on another. I need to move one record from the production server into the test table on a different server. I setup the production server as a linked server one the test box. I can read/select fine, but when I try to insert I get an error. Is there additional setup I am missing?

INSERT INTO testdta.f4801 SELECT * FROM OPENQUERY(jdeprod, 'SELECT * FROM proddta.f4801 where wadoco=3037086')

Server: Msg 7391, Level 16, State 1, Line 1

The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.

[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]

OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a]

Re: insert data between SQL servers
 

I think you use Transaction. So you must start Distributed Transaction Coordinator on both server.

Re: insert data between SQL servers
 

I found a microsoft paper explaining to turn on DTC on both servers, it was only on one server. Once I did this setup, I was able to insert data from prod server to dev server. Thanks for the reply.


http://support.microsoft.com/default...b;en-us;816701