MS SQL

insert data between SQL servers

Please support our MS SQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Apr 2008

Posts: 4

Reputation: kbrown123 is an unknown quantity at this point

Solved Threads: 0

kbrown123

Offline

Newbie Poster

insert data between SQL servers

Apr 18th, 2008

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?

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
INSERT INTO testdta.f4801 SELECT * FROM OPENQUERY(jdeprod, 'SELECT * FROM proddta.f4801 where wadoco=3037086')
INSERT INTO testdta.f4801 SELECT * FROM OPENQUERY(jdeprod, 'SELECT * FROM proddta.f4801 where wadoco=3037086')

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
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]
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]

Last edited by peter_budo; Apr 18th, 2008 at 11:50 am. Reason: Keep It Organized - please use [code] tags

•

Join Date: Feb 2008

Posts: 36

Reputation: huangzhi is an unknown quantity at this point

Solved Threads: 12

huangzhi

Offline

Light Poster

Re: insert data between SQL servers

Apr 22nd, 2008

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

Last edited by huangzhi; Apr 22nd, 2008 at 12:08 am.

Hence Wijaya
www.ex-Soft.tk

•

Join Date: Apr 2008

Posts: 4

Reputation: kbrown123 is an unknown quantity at this point

Solved Threads: 0

kbrown123

Offline

Newbie Poster

Re: insert data between SQL servers

Apr 22nd, 2008

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