2

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.

Improve this question

2 Answers 2

Reset to default
2

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

enter image description here

Improve this answer
4
  • What security or other configuration risks can I incur by enabling this?
    – J.D.
    Commented Feb 28, 2020 at 20:44
  • As far as I am aware there is no configuration or security risk. But please see here for more details: learn.microsoft.com/en-us/previous-versions/sql/…
    – Select 'DBA'
    Commented Mar 2, 2020 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'
    Commented Mar 2, 2020 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.
    Commented Mar 2, 2020 at 15:18
1

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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