Friday, 27 June 2014

1.20 - SQL 2012 linked server query and loading a result set into an object

I recently upgraded an environment into a SQL 2012 environment from SQL 2008. Today one of the devs could not run a distributed query to one of the linked servers, but only if it returned a result set into a temporary table or table variable.
The query was running a Stored procedure that returned a small subset of data, It would run perfectly fine on its own but as soon as it ran with the additional task of adding the data into a temp table it blew up with the error
The partner transaction manager has disabled its support for remote/network transactions. and followed up with The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "[SERVERNAME]" was unable to begin a distributed transaction.

I checked the configuration of the new servers and MS DTC was configured in the exact same way from the old ones but for some reason SQL 2012 seemed to be dealing with it differently (or at least the linked server provider was).
After I amended the local DTC config to accept network DTC access and for remote clients(inbound and outbound) all was well.