I have link a server with another server. I can access records from linked server and I can also insert record using INSERT statement like
INSERT INTO [LINKED-SERVER-IP].MyDb.dbo.Customer (CustomerId, CustomerName)
SELECT CustomerId, CustomerName FROM MyCustomers WHERE CustomerId = 5
Above query work ok and insert record but when I use this query in Trigger I get following error.
OLE DB provider "SQLNCLI" for linked server "Linked-Server-IP" returned message "The partner transaction manager has disabled its support for remote/network transactions.".
Some error occoured.
Msg 3616, Level 16, State 1, Line 1
Transaction doomed in trigger. Batch has been aborted.
Thanks.
Best Answer
Code in triggers runs inside the same implicit transaction as the insert/update/delete statement. Therefore, it's trying to share the transaction across both servers, which is disallowed by default.
Do you have enough privilege to change settings? If not, then you could consider other options such as inserting into a local table and replicating the data across, or use Service Broker to get another process to do the remote insert.