1

This is the error message I'm receiving:

Msg 7391, Level 16, State 2, Procedure spStoredProc, Line 62 [Batch Start Line 1] The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "MyLinkedServer" was unable to begin a distributed transaction.

As a test my stored procedure query is just SELECT 1 AS A and works locally on the server but doesn't work when I call it remotely on a linked server.

0

On the Linked Server properties have you tried setting "Enable Promotion of Distributed Transactions" to False?

enter image description here

4
  • What security or other configuration risks can I incur by enabling this? – J.D. Feb 28 '20 at 20:44
  • As far as I am aware there is no configuration or security risk. But please see here for more details: docs.microsoft.com/en-us/previous-versions/sql/… – Select 'DBA' Mar 2 '20 at 15:09
  • 'remote proc transaction promotion' is a new option on SQL Server 2008, which allows you to control whether or not you want to enlist remote stored procedure call in a distributed transaction. When this option is off (FALSE), the local transaction will not be promoted to distributed transaction. This is how we are able to separate outer and inner transactions in a "autonomous transaction" fashion. – Select 'DBA' Mar 2 '20 at 15:12
  • Gotcha, yea I did read that but wasn't 100% sure how to digest it. Does setting that option to false essentially just mean that if the transaction on the remote server fails it won't rollback the transaction on the local server? Can this affect AlwaysOn AG data synchronization? – J.D. Mar 2 '20 at 15:18
0

You need to have MSDTC (distributed transaction coordinator) running on all the machines involved. See https://stackoverflow.com/questions/7694/how-do-i-enable-msdtc-on-sql-server.

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.