Sunday 22 July 2018

Linked Server Setting - Promotion of Distributed Transactions

This error occured when I tried to access a stored proedure via a linked server.
Getting the results worked fine, simply executing the stored procedure.

EXEC [SERVERNAME].[DatabaseName].[SchemaName].[myStoredProcedure]

But putting the results into a temporary table meant it became a 'distributed transaction'

CREATE TABLE ##tempTable (column1, column2...)
INSERT INTO ##tempTable
EXEC [SERVERNAME].[DatabaseName].[SchemaName].[myStoredProcedure]

OLE DB provider "SQLNCLI10" for linked server "SERVERNAME" returned message "The partner transaction manager has disabled its support for remote/network transactions.".
Msg 7391, Level 16, State 2, Line 12
The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "SERVERNAME" was unable to begin a distributed transaction.

Both servers were already running the Distributed Transaction Coordinator (DTC) service.
Multiple posts suggest tuning the security of DTC, but I eventually found the solution was much simpler. Like the setting for Remote Procedure calls RPC, it turned out to be a property of the linked server itself, 'Enable Promotion of Distributed Transactions'.

The default for 'Enable Promotion of Distributed Transactions' is TRUE, setting it to FALSE enabled my code to run.

No comments: