SQL Server 2005 trigger to update remote server

Question

I have created a trigger that updates an remote SQL Server 2005 database table. The code works if it is a manual query but will not work in a trigger because it tries to place it in a transaction and fails because of MSDTC.

Our internal server has appropriate access to the remote server but the remote server does not have access to the internal server, would this cause the trigger to fail?

The error I get is:

OLE DB provider "SQLNCLI10" for linked server "LinkServer" 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 "SQLNCLI10" for linked server "LinkServer" was unable to begin a distributed transaction.

I have looked this up and set everything as suggested but no joy?

Answer 1

And what if the remote server is unavailable for any reason?

You should generally avoid performing any action in a trigger that works beyond the database, let alone the server. The issue is that everything that happens within the trigger has to be part of the original transaction, and if anything goes wrong in the trigger, that transaction will be rolled back.

You do not, usually, want this to happen if there's an intermittent problem reaching a remote resource (be that a different DB on the same server/instance, or something truly remote).

Have you looked into decoupling this activity with, for instance, Service Broker?