Using Synonym to Update Linked Server Data

• James123

  SSC Veteran

  Points: 284

  More actions

  December 15, 2014 at 8:29 am

  #293575

  Hello,

  I have created a linked server and a synonym so I can do the following

  SELECT * FROM LINK.Table WHERE Flag=0

  However, when I try to update the table with:

  UPDATE LINK.Table

  SET Flag=1

  WHERE ID=1

  I get "The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "MYSERVER" was unable to begin a distributed transaction."

  I have checked the RPC options for the linked server and they are set to true.

  Can anyone tell me why I can select but not update please?

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  December 15, 2014 at 8:40 am

  #1764732

  Is the Distributed Transaction Coordinator running on both servers?

  John

• James123

  SSC Veteran

  Points: 284

  More actions

  December 15, 2014 at 8:48 am

  #1764736

  Hi John,

  I've just spotted the problem. The code sample I sent was being executed from within a sproc and that sproc was wrapped in a transaction. When I remove the transaction the sproc runs fine.

  Thanks for your reply.

  Regards

  James