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

Question

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.

score 2 · Answer 1 · answered Feb 25 '20 at 00:04

2

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

answered Feb 25 '20 at 00:04

Select 'DBA'

165
2
11

score 1 · Answer 2 · answered Feb 26 '20 at 15:44

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.

answered Feb 26 '20 at 15:44

user3777284

61
3

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

2 Answers2