What configurations do I need to fix the "unable to begin a distributed transaction" error when trying to run a remote procedure?

by J.D.   Last Updated June 30, 2020 04:06 AM

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.

Answers 2

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

enter image description here

Select 'DBA'
Select 'DBA'
February 25, 2020 00:04 AM

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.

February 26, 2020 15:44 PM

Related Questions

What's an example of a Distributed Database Query?

Updated March 09, 2017 08:06 AM

Where does linked server query gets executed?

Updated August 17, 2017 15:06 PM