3

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.

Kashif
  • 193
  • 2
  • 11

1 Answers1

2

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.

Rob Farley
  • 688
  • 2
  • 5
  • 13
  • Thanks Rob Farley, can you please tell me that what settings I have to change? – Kashif Jun 23 '10 at 09:35
  • @Muhammad, I'd go with Rob's solution about replication or using the Service Broker. I'd sooner see that than changing the settings. Books Online is your friend. –  Jun 23 '10 at 23:59
  • Hi Muhammad. There's an article at http://msdn.microsoft.com/en-us/library/ms191156.aspx which may help. But you'll probably find it easier to handle it with replication or SB. – Rob Farley Jun 24 '10 at 02:58