SQL-Server trigger update issue from over linked server

Question

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

Ondrej Vencovsky · Accepted Answer · 2010-11-13 12:42:55Z

1

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

answered Nov 13, 2010 at 12:42

Ondrej Vencovsky

3,5689 gold badges32 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kerberos Over a year ago

Yes, it installed on local server and "Distributed Transaction Coordinator" servis statue is "started". But if you ask about linked server, i don't know. Because i could'nt access this server.

StKiller · Accepted Answer · 2011-11-30 08:53:44Z

0

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

edited Nov 30, 2011 at 8:53

StKiller

7,99110 gold badges45 silver badges57 bronze badges

answered Jul 13, 2011 at 6:17

Garima

1

2 Comments

bobs Over a year ago

This doesn't appear to be the answer. The original poster (OP) already provided the answer in an Edit to the question.

Saroop Trivedi Over a year ago

There is three DNS.. USER DNS,System DNS and File DNS whixch one I will use

Collectives™ on Stack Overflow

SQL-Server trigger update issue from over linked server

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related