Phidiax Tech Blog

Regularly, I encounter the common battle between the BizTalk Adapter (WCF-SQL/WCF-Custom) and SQL Stored Procedures causing contention over transactional scope. I am sure that most Developers and Architects who have constructed integration solutions, which have SQL context, have dealt with this nebulous scenario. I decided that I was going to dig deep into the cause of this, develop a proper understanding of the technicals, and provide the community a write up so that we all can deliver better structured solutions. 

So here is the first lesson learned: 

STOP! Do not set useAmbientTransaction to FALSE, it fix may cause data loss and duplicate data.

[Error Description: System.Data.SqlClient.SqlException (0x80131904): Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.]

Below discusses the correct way to resolve the database connection error commonly found while using the WCF-SQL/WCF-Custom Adapter.

When you initiate a transaction through the BizTalk Adapter, BizTalk attempts to make sure that the Receive from SQL Server completes successfully. This means, from the time the BizTalk Adapter starts receiving the data, to the time that the message is inserted into the MessageBox.

Transactional Scope - SQL

To initiate a transaction solely inside of the SQL Stored Procedure would ignore the existing BizTalk/MSDTC transaction. This mistakenly and unnecessarily tells SQL Server that the transactional state is solely in the control of the stored procedure. Hence, a sole SQL Transaction within the SQL Procedure is unnecessary.

Transactional Scope - Calling Application (BizTalk)

By allowing the calling application (BizTalk) to initiate a Distributed Transaction, it tells SQL Server that this is a transactional call. This is enabled by using the correct settings for the Transaction Isolation level of the BizTalk Adapter.

Manage the Fault Chain

To abort the transaction, the stored procedure only needs to create a fault. This will trigger the entire transaction chain, from SQL Server, through the BizTalk process, and to the MessageBox. Ultimately, this manages a complete roll back.

Also, this means that any component can fail, this includes:

• MessageBox
• Host process instance that is processing the data
• SQL Server that is providing data

The entire transaction will roll back and the next process can continue where it left off, guaranteeing ACID for the transaction. 

Solution

Here are the necessary configuration settings:

1. Use the correct ACID level in your transaction, generally BizTalk Adapter setting "READ COMMITTED" is fine, and supports high speed parallel transactions with multiple BizTalk Servers hitting the table
2. Enable the BizTalk Adapter setting "useAmbientTransaction," which will flow the transaction from BizTalk into the SQL Server and the MessageBox
3. Use checks in your stored procedure to throw exceptions when things go wrong. SQL Functions like "RAISERERROR," will abort the transaction for you.
4. Do not explicitly set your transaction isolation level in the SQL stored procedure
5. Do not start, commit, or abort transactions in your SQL stored procedures
   

• Error occurs in receive port: Set the transactionIsolationLevel to ReadCommitted

• Error occurs in send port: Set the Messages Transaction Level to ReadCommitted

If above options do not solve the issue, I would suggest you to look further into the MSDTC configuration on the SQL Server as well as the transactional code in stored procedure.

The Most Common Mistake

The most common mistake is configuring the BizTalk Adapter setting "useAmbientTransaction=False." This turns off the behavior defined in the Solution section and essentially pushes all errors to the SQL Server. Thus, if anything goes wrong, such as timeout errors or lock timeouts, they will not be detected by BizTalk. Furthermore, if that error indicates that the data couldn't be inserted, then that data is lost.

Conclusion

A common fix is to set the useAmbientTransaction property (of WCF-Custom Transport) to False.  However, Microsoft does not recommend setting this property to False because it will cause duplicate messages and message loss.  (Working with BizTalk Adapter for SQL Server Binding Properties)