DISTRIBUTED TRANSACTION - errors and fixes

Open DISTRIBUTED TRANSACTION between servers

http://copypastenet.blogspot.co.il/2016/06/transaction-between-servers-distributed.html

OLE DB provider "SQLNCLI11" for linked server "" returned message "Cannot start more transactions on this session."

http://copypastenet.blogspot.co.il/2016/06/ole-db-provider-sqlncli11-for-linked.html

The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction.

http://copypastenet.blogspot.co.il/2016/06/the-microsoft-distributed-transaction.html

The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction

Try to do:

DISTRIBUTED TRANSACTION in try-catch block.

Error message:

The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction.

Solution:

remove the try-catch block and see the exact error. then fix it - it probably some 'standard' SQL error.

but - if you want/need to see the errors:

run dynamic sql from the linked server to the current one:

http://copypastenet.blogspot.co.il/2016/06/run-dynamic-sql-from-linked-server-to.html

OLE DB provider "SQLNCLI11" for linked server "" returned message "Cannot start more transactions on this session."

Try to do:

DISTRIBUTED TRANSACTION

Error message:

OLE DB provider "SQLNCLI11" for linked server "" returned message "Cannot start more transactions on this session."

Solution:

In order to enable the distributed transactions set:

SET XACT_ABORT ON;

Run dynamic sql from the linked server to the current one

SET XACT_ABORT ON; -- In order to enable the distributed transactions

BEGIN DISTRIBUTED TRANSACTION

DECLARE @SQL nvarchar(max)

BEGIN TRY

       set @SQL = 'REVERT; EXECUTE AS LOGIN = ''SA''

              EXECUTE(''

       UPDATE / INSERT / .....

       FROM [LinkedServerDataBaseName].[LinkedServerSchemaName].[LinkedServerTableName] b

       JOIN [MyServerNameAsLinkedServerName].[DataBaseName].[SchemaName].[TableName] ns  ON b.BRN_ID = ns.BRN_ID

       WHERE .......

              '') AT [LinkedServerName]';

       exec sp_executesql @SQL

       COMMIT TRANSACTION

END TRY

BEGIN CATCH

       -- ROLLBACK TRANSACTION

       -- do something....

END CATCH

SET XACT_ABORT OFF;

LinkedServerDataBaseName - database name in the linked server

MyServerNameAsLinkedServerName - my server has to be declared as linked server in the other server, this is it's linked server name there.

LinkedServerName - the linked server name in my server

TRANSACTION between servers - DISTRIBUTED TRANSACTION

SET XACT_ABORT ON; -- In order to enable the distributed transactions

BEGIN DISTRIBUTED TRANSACTION

-- do something

ROLLBACK/COMMIT TRANSACTION

SET XACT_ABORT OFF;