CODEWITHSUNDEEP
sql-server-2008sql-server-2005.net-2.0asp.net-2.0linked-server
sammiiuu
sammiiuu

Reputation: 107

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

Background: We have a website that was developed under.NET framework 2.0 and it uses a database created with Sql Server 2005. And we also have a website that was developed under .NET framework 4.0 and this one uses a database created with Sql Server 2008. We linked both databases (from Sql Server 2005 to Sql Server 2008) creating a “linked Server on the Sql Server 2005 database”.

Scenario: When we create a record on the database hosted in Sql Server 2005 we have this sequence of transactions: Insert the record into the table hosted in the Sql Server 2005 database. This insertion executes a trigger after inserting. Inside the trigger, we execute a stored procedure (hosted in Sql Server 2008 database), and this stored procedure is executed by using the “linked Server”. This stored procedure performs an insertion into a table hosted in the Sql Server 2008 database. If we manually execute the insertion into the table hosted in Sql Server 2005 database using the Sql Server Management Studio 2005, everything gets executed well, but if we try to create the record using the UI of the web site (.NET 2005) we get this error:

The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction. The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction.

Does anyone know what is happening?

Upvotes: 1

Views: 7666

Answers (1)

Jānis
Jānis

Reputation: 2266

I do find very risky to execute such code in trigger. I would consider removing trigger and creating procedure that executes actions described.

And to test correctly everything you should either call DML action in one transaction on both servers or you should begin distributed transaction explicitly by calling "begin distributed tran". If you just update something on other server (only 1 action) you are not using distributed transaction and test is not correct.

Upvotes: 0

Related Questions