Post reply

Using Synonym to Update Linked Server Data

  • 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?

  • December 15, 2014 at 8:40 am

    #1764732

    Is the Distributed Transaction Coordinator running on both servers?

    John

  • 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

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply