SQL Server Linked Server – MSDTC on Cluster Configuration

clusteringlinked-servermsdtcsql serversql-server-2012

I'm trying to set up a linked server between two instances we have on our network. I've done this many times before without an issue, but this time I repeatedly get the following error:

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

I've got server 'A', a SQL2008 R2 box and server 'B' a SQL2012 box as part of a cluster.

I've setup the linked server on server A and I can run a simple select * from [linkedserver].dbname.dbo.table which works correctly.

What isn't working for me, is a a trigger on a table on DB 'A' which is activated from an SP on server A attempting to insert into a table on DB 'B'.

Has anyone got any ideas as to why a query would work, but a trigger fired from an SP wouldn't?

Best Answer

Have you configured MSDTC on both the source and target machine using dcomcnfg?

The distributed transaction coordinator, by default, does not allow distributed transactions.

You need to configure it by running dcomcnfg as an administrator, and configure the options applicable to your situation:

enter image description here

Related Question