distributed-databasesdistributed-queriessql serversql-server-2016stored-procedures
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.
Someone posted a solution to a related problem on StackOverflow:
https://stackoverflow.com/questions/5945435/sql-server-2000-linked-server/6651704#6651704
In addition to setting Local DTC Properties, you need to open the firewall on both machines to allow program Distributed Transaction Coordinator to go thru.
When testing T-SQL in Query Editor Window, make sure to turn on XACT_ABORT as suggested by Microsoft.
XACT_ABORT must be set ON for data modification statements in an implicit or explicit transaction against most OLE DB providers, including SQL Server. The only case where this option is not required is if the provider supports nested transactions.
In my case, for the Local DTC Properties, I only need these:
Best Answer
On the Linked Server properties have you tried setting "Enable Promotion of Distributed Transactions" to False?