The saga of working with error messages continues. Here is an error that was shared at one of the User group meetings by a member. While working via linked server to do data manipulation they reported this error on their servers:
Msg 1206, Level 18, State 118, Line 9 The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction.
Initially I thought it was due to misconfigured DTC between source and destination, but digging more into this error revealed something really interesting and again I learned something new. Hence this blog post was born.
Whenever I get any DTC errors while working with linked server, the very first test I perform is by doing the dummy distributed transaction to see whether DTC between two servers is working or not.
BEGIN DISTRIBUTED TRANSACTION
SELECT * FROM <linkedServer>.<DatabaseName>.dbo.TestTable
If above test is failing, then make sure you follow the KB article 2027550 and make setting as shown below:
Below are some errors which I have seen in this regards in the past:
- OLE DB provider “SQLNCLI11” for linked server “linkedservername” returned message “No transaction is active.”
- The operation could not be performed because OLE DB provider “SQLNCLI11” for linked server “linkedservername” was unable to begin a distributed transaction.
In the error message for which I was consulted, the DBA/Developer confirmed the above test was successful. Moreover, the setting were are per above screenshot too. So there was something else causing the DTC error which made me curious. I asked what type of code raised this sort of error.
Here was the piece of code which was failing as per the Developer. I have done simplification of the code for your reference.
SET XACT_ABORT ON
BEGIN DISTRIBUTED TRANSACTION
INSERT INTO SQLLinkedServer.SQLAuthority.dbo.TestTable VALUES (1,'MyFirstName')
IF @@Trancount > 0
SET XACT_ABORT OFF
In above script, SQLLinkedServer is the name of linked server, SQLAuthority is the name of the database located on the linked server instance.
We captured profiler and found that the actual error was something else which was not shown due to the fact that we have used TRY…CATCH block. If we remove the block, we can see exact error.
The actual error is masked due to the way TRY… CATCH works. As per this – This is more of a limitation with the ERROR functions since they can return only one error & hence the last one.
To reproduce the error, I have already inserted a record in table residing under database on linked server. That’s why we are seeing primary key violation error.
Though this is an interesting scenario to investigate, I generally try to understand why we are using Linked Server and if there are ways to mitigate the same. But that will be a different discussion for some other blog post. If you ever encountered this error, do let me know what ways in which you found a solution.
Reference: Pinal Dave (https://blog.sqlauthority.com)