Invoke a linked server from a trigger

Question

I created instead of trigger on server A, in which I call a procedure, which in turn calls another procedure on the linked server (server B).

Trigger (A) → Procedure (A) → Procedure (B)

But, when the trigger is triggered, it raises the following error:

OLE DB provider "SQLNCLI11" for linked server "xxx" returned message "Partner Transaction Manager has disabled remote / network transaction support." Msg 7391, Level 16, State 2, Line 1 The operation could not be completed because the OLE DB provider "SQLNCLI11" for linked server "xxx" could not start the distributed transaction.

When I do this: Procedure (A) → Procedure (B) works fine. What could be the reason?

How can i fix this? Is a distributed request different from a linked server request?

Answer 1

You need to use DTC if you are using linked triggers (not recommended as it does not work well and your transaction will be aborted if there are any connection problems)

or

your procedure (A) can populate some queue table, and then some scheduled task can read this queue table and start procedure B.

or

use replication or CDC (data shift) + scheduled task to run procedure B on the remote server

or

use local service broker