SQL-Server trigger update issue from over linked server

I would like to update rows over linked server. I created linked server on my local SQL-Server with this instruction.

I can update rows over Query Analyzer with below code. No error returned and rows update on Linked Server successfully.

UPDATE  [LinkedServer].[Database].[dbo].[Table]
SET A = 'A', B = 'B'
WHERE id = 1

But when I created a update trigger to my table on my local sql server like below,

ALTER TRIGGER [TriggerLocalServerTable]
   ON  dbo.[LocalServerTable]
    FOR UPDATE
AS 

DECLARE @A varchar(4)
DECLARE @B varchar(4)
DECLARE @id int

BEGIN
 SELECT
 @A = A,
 @B = B,
 @id = id
 FROM inserted

 UPDATE  [LinkedServer].[Database].[dbo].[Table]
 SET A = @A, B = @B
 WHERE id = @id      
END

When trigger performed, returns this error like below,

OLE DB provider "SQLNCLI" for linked server "LinkedServer" returned message "The partner transaction manager has disabled its support for remote/network transactions.". Msg 7391, Level 16, State 2, Procedure TriggerLocalServerTable, Line 45 The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "LinkedServer" was unable to begin a distributed transaction.

I applied this instruction to local server for solving issue, anything changed.

More details;

• if defined my linked server with its remote ip address.
• My local server and Linked Server not in same domain.
• Linked Server Security setting is "Be made using this security context"
• Local server version is Windows Server 2003 SP2, SQL-Server version is 2005 standart.
• Linked server OS version is 2008 and SQL-Server version 2008 (64 bit).
• I couldn't access remote server

  with RDP. i can access just from sql port (1433).

• i set linked server "RPC" and "RPC Out" to "True" from "Security Option".

Do you have any idea to solve this issue. Thank you very much already now.

Edit: I solved this issue. Firstly i created a trigger for update on [TriggerLocalServerTable]. This trigger's job is inserting new update fields to a local tab开发者_如何学Gole. This new table using for updated temp datas. Then i created a job that runs every hour for update fileds on LinkedServer. This job gets data from temp table then update to LinkedServer table's fields.

Regards, Kerberos.

Do you have a Distributed Transaction Coordinator installed on Windows Server? If your update is inside a transaction, it won't work without it. See link text

The problem dont lies with SQL Server it is with ODBC Driver.

• Go to Start > Settings > Control Panel > Administrative Tools > Data Sources
• Open System DSN Tab
• Select the driver you have used for Linked Server and click configure
• Select Advanced > Flags3 and Uncheck Disable transaction check box

Problem Solved :)