Calling linked server from trigger

问题:

I've created instead of trigger in server A, in which I call a procedure, which in turn calls other procedure in linked server (server B).

Trigger(A) -> Procedure (A) -> Procedure (B)

But, when trigger executes it throws following error:

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

When I do this: Procedure (A) -> Procedure (B) it works perfectly. What can be the reason?

How can I fix this? Is distributed query differ from Linked server query?

回答1:

You need to use DTC if you use linked servers within a trigger (not recommended as it's not performing well and your transaction will be aborted if there are any connection issues)

or

your Procedure (A) may populate some queue table, and then some scheduled task may read that queue table and run Procedure B.

or

use Replication or CDC (change data capture) + scheduled task to trigger Procedure B on remote server

or

use Local Service Broker