CODEWITHSUNDEEP
sqlsql-serverlinked-server
Kerberos
Kerberos

Reputation: 1266

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;

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 table. 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.

Upvotes: 2

Views: 12446

Answers (2)

Garima
Garima

Reputation: 1

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 :)

Upvotes: 0

Ondrej Vencovsky
Ondrej Vencovsky

Reputation: 3498

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

Upvotes: 1

Related Questions