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

Applies to: BizTalk Adapters

This article applies to the following product:
  • Microsoft BizTalk Adapter Pack 2.0
  • Microsoft BizTalk Adapter Pack 2010
  • Standalone WCF-based BizTalk Adapter for SQL Server

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


Cumulative update information

BizTalk Adapter Pack 2010

The hotfix that resolves this issue is included in Cumulative Update 1 for BizTalk Adapter Pack 2010. 

For more information about how to obtain the cumulative update package, click the following article number to view the article in the Microsoft Knowledge Base:
     2539794 Cumulative update package 1 for BizTalk Adapter Pack 2010

BizTalk Adapter Pack 2.0

The hotfix that resolves this issue is included in Cumulative Update 2 for BizTalk Adapter Pack 2.0.  

For more information about how to obtain the cumulative update package, click the following article number to view the article in the Microsoft Knowledge Base:
2531605 Cumulative update package 2 for BizTalk Adapter Pack 2.0

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: