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
ROLLBACK TRANSACTION
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
GO
BEGIN DISTRIBUTED TRANSACTION
BEGIN TRY
INSERT INTO SQLLinkedServer.SQLAuthority.dbo.TestTable VALUES (1,'MyFirstName')
END TRY
BEGIN CATCH
IF @@Trancount > 0
ROLLBACK TRAN
END CATCH
GO
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)
11 Comments. Leave new
You must 2 instances
Hi I got some issue regarding to MS DTC, We have two sql server 2012 and they used the link server connected.
when we join two table from two server with select query and it will come with the error
error 3898 New request is not allowed to start because it should come with valid transaction descriptor.
If we added “BEGIN DISTRIBUTED TRANSACTION” and “COMMIT TRANSACTION” then
the select query can run without any problem
do you have any idea for the issue?
In your try..catch, should you not rethrow the exception?
I had a similar problem and understand the issue as follows:-
Technical Explanation
—————————
The issue is between DTC and all SQL Servers involved in the distributed transaction … the timing issue clarifies the ‘randomness’ of the returned error message.
e.g. you sometimes get a meaningful error generated by your TRY/CATCH
Msg 50000, Level 16, State 1, Procedure spd_IGMS_AddToAMSBranchApplication_Matt, Line 125
Conversion failed when converting the varchar value ‘b100’ to data type int. Details: [Stored Procedure
and other times the CATCH is not executed, instead DTC aborts the procedure:
Msg 1206, Level 18, State 118, Procedure spd_IGMS_AddToAMSBranchApplication_Matt, Line 254
The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction.
It appears rely on if DTC (remember a separate process) gets in before your SQL code gets a chance to execute the CATCH.
The entire problem stems from the fact that DTC uses ‘attention’ singles (e.g. the same as client applications) to terminate transactions. Attention Singles are not detected by TRY/CATCH
This behavior of DTC is highlighted a little by Microsoft TRY/CATCH documentation. S
If a distributed transaction executes within the scope of a TRY block and an error occurs, execution is transferred to the associated CATCH block. The distributed transaction enters an uncommittable state. Execution within the CATCH block may be interrupted by the Microsoft Distributed Transaction Coordinator which manages distributed transactions. When the error occurs, MS DTC asynchronously notifies all servers participating in the distributed transaction, and terminates all tasks involved in the distributed transaction. This notification is sent in the form of an attention, which is not handled by a TRY…CATCH construct, and the batch is ended. When a batch finishes running, the Database Engine rolls back any active uncommittable transactions. If no error message was sent when the transaction entered an uncommittable state, when the batch finishes, an error message will be sent to the client application that indicates an uncommittable transaction was detected and rolled back For more information about distributed transactions, see Distributed Transactions (Database Engine).
Also I found a closed (and unresolved) incident on Mircrosoft Connect. Microsoft has unfortunately given the following as a response:
“We looked at the interaction of DTC and DML for this particular error & reached conclusion that we cannot fix this behavior”
See full response here: https://docs.microsoft.com/en-us/collaborate/connect-redirect
In simple terms…
Layman’s explanation
—————————
TRY/CATCH
A “TRY” is like a friendly cashier at the supermarket who tries to process your grocery transaction, tells you that you have the wrong code for your bananas and directs you to the grocery manager (the “CATCH”).
DTC Attention Signal
A DTC “Attention Signal” is more like if the cashier was a gangster and when he realizes you have the wrong code for your bananas he simple pulls out his AK47 and blows your groceries away without explaining why (your banana code was wrong).
:-)
Thanks for such a detailed Matt.
No worries Pinal, Merry Christmas ?
Same to you @Matt
Hello Team,
I am getting “was unable to begin a distributed transaction” only if i execute DML query through trigger but not if i run it through sp and the sp if is use in trigger i am getting same error. Any ideas what am i missing?
you have DTC verified settings?
Awesome … this was my EXACT situation, and only after reading this blog and removing my try/catch, was I able to see the actual source exception (which happened to be an FK violation, resulting from a typo in my code). I was banging my head over DTC for a good 24 hours before I found this write-up! Thank you very much !
Clay
Interesting…I found this post because of the MSDTC error but in my situation the stored procedure was running code across linked servers with a try/catch block and reporting a false error about not being able to execute msdb.sp_send_dbmail. I stripped the code from the procedure and removed the try/catch and then the MSDTC error surfaced [MSG 1206, Level 18, State 118…The microsoft distributed transaction coordinator has cancelled the distributed transaction.] It has to have something to do with security in my environment because if you have sysadmin everywhere then it will run.