5

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):

Local DTC Properties

but sq009 still is apparently complaining.

  • Check this and this. – SqlWorldWide Apr 3 '18 at 1:27
  • @SqlWorldWide thanks for the links but they don't appear relevant – Conrad Apr 3 '18 at 14:23
2

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

  • Sorry I can't comment on this - the DBA who owns the servers fixed the problems, but won't tell me what he did. 😒 – Conrad Apr 18 '18 at 12:37
  • 1
    @Conrad, there's a special place in Hell for your DBA. – easuter Feb 13 at 17:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.