Have you ever encountered an issue related to MS DTC, SQL Server, and JDBC XA distributed transactions? In this article, we will explore a common error that occurs when using these technologies together and discuss a possible solution.

A client recently contacted me for help with an error they were experiencing in their application. The error message they provided was:

Caused by: javax.transaction.xa.XAException: com.microsoft.sqlserver.jdbc.SQLServerException: Failed to enlist. Error: "Import of Microsoft Distributed Transaction Coordinator (MS DTC) transaction failed: 0x8004d00e(XACT_E_NOTRANSACTION)."

Upon further investigation, we discovered that the error was caused by a failure to enlist the transaction with the MS DTC. To troubleshoot this issue, we examined the SQL Server error log and found the following messages:

Attempting to load library 'SQLJDBC_XA.dll' into memory.
Using 'SQLJDBC_XA.dll' version '0004.00.2206' to execute extended stored procedure 'xp_sqljdbc_xa_start'.
Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC).
Recovery of any in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC) has completed.
Error: 8509, Severity: 16, State: 1.
Import of Microsoft Distributed Transaction Coordinator (MS DTC) transaction failed: 0x8004d00e(XACT_E_NOTRANSACTION).

It became clear that the issue was related to the MS DTC and its interaction with SQL Server. To resolve this problem, we followed a checklist of steps:

1. Go to “Administrative Tools > Component Services” (or Start > Run > DcomCnfg > Enter).
2. In the left navigation tree, go to “Component Services > Computers > My Computer > Distributed Transaction Coordinator > Clustered DTCs”.
3. Right-click on the DTC service for the SQL Server group and select “Properties”.
4. Go to the security tab and check “Network DTC Access”, “Allow Inbound”, and “Allow Outbound”.

After performing these steps, we still encountered the same error. However, we realized that since it was a clustered DTC, there might be an issue with the DTC and SQL mapping in the cluster.

After researching various resources, we found a solution. We used the following three commands to fix the mapping:

View the mapping: msdtc -tmmappingview *
Clear the mapping: msdtc.exe -tmMappingClear -name DTC_INST02_Mapping
Create correct mapping: Msdtc -tmMappingSet -name DTC_INST02_Mapping -service "MSSQL$INST02" -ClusterResourceName "MSDTC-INST02"

It is important to note that if you are not using SQL JDBC XA, you do not need to set TM mappings. SQL Server 2008 knows which clustered DTC instance it needs to use. TM mappings are stored in the cluster registry, which is shared across all nodes, so they can be set from any of the cluster nodes.

In conclusion, when encountering issues with MS DTC, SQL Server, and JDBC XA distributed transactions, it is crucial to check the DTC settings, security permissions, and the DTC and SQL mapping in the cluster. By following the steps outlined in this article, you can resolve the error and ensure smooth transactional operations in your application.