Two servers: sq009 and sq202, both running SQL Server 2012 SP3.
sq202 is the SQL Server hosting the DB for our ERP system, Sage 500.
sq009 is the SQL Server hosting the DB for our IBM Maximo instance.
The situation: a trigger exists on a table in sq202, which should insert a row in the sq009 DB when a row is inserted in the sq202 DB. However, I get this error in Sage 500 dialog when the record is inserted:
The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "sq009" was unable to begin a distributed transaction.
OLE DB provider "SQLNCLI11" for linked server "sq009" returned message "The transaction manager has disabled its support for remote/network transactions.".
Both servers have the other as a Linked Server; both have all connections being made using a specified security context; both have the server option Enable Promotion of Distributed Transactions for RPC set to False.
I have confirmed the Distributed Transaction Coordinator service is running on sq009, and set its permissions appropriately I believe (even though I understand this shouldn't be needed):
but sq009 still is apparently complaining.
Best Answer
Try enabling the DTC on the sq202 box, since that is the server initiating the distributed transaction. You will also need to have Enable Promotion of Distributed Transactions for RPC option set to True as well for the sq009 linked server located on the sq202 box; when this option is TRUE, calling a remote stored procedure starts a distributed transaction and enlists the transaction with MS DTC.
Helpful information on T-SQL Distributed Transactions and the requirements: https://technet.microsoft.com/en-us/library/ms191156(v=sql.105).aspx
Linked Server Options: https://technet.microsoft.com/en-us/library/ms186839(v=sql.105).aspx