FIX: A WCF-based SQL adapter may lock SQL Server resources when the PolledDataAvailableStatement binding property returns a positive result but the actual polling does not return any data

Symptoms

Consider the following scenario:

• You have a computer that is running Microsoft BizTalk Server 2006 R2, Microsoft BizTalk Server 2009 or Microsoft BizTalk Server 2010.

• You create several receive locations that use a Windows Communication Foundation (WCF)-based SQL adapter to access data in a SQL Server database. For example, you use the WCF-Custom adapter together with the sqlBinding binding type or the WCF-SQL adapter from BizTalk Adapter Pack 2.0 or BizTalk Adapter Pack 2010.

• You specify a statement in the PolledDataAvailableStatement binding property that returns a positive value for the row count.

• Before the adapter starts polling data from SQL Server, the SQL Server database is updated. This makes the PolledDataAvailableStatement binding property return a null value.

In this scenario, the WCF-based SQL adapter may lock the database's SQL resources for over 30 minutes. This may block the activities of other SQL clients on the database.

Cause

The issue occurs because, when the polling starts to receive data, it opens a transaction. But when the transaction returns no data, the polling continues to look for data within the transaction. This may block other SQL clients from using the same underlying resources.

Resolution

Status

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

References

For more information about how to receive poling data from SQL server, by using WCF Channel, visit the following MSDN webpage:

Receiving Polling-based Data-changed Messages from SQL Server by Using the WCF Channel Model