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

Article translations Article translations
Article ID: 2481676 - View products that this article applies to.
Notice
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
Expand all | Collapse all

On This Page

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:
Receiving Polling-based Data-changed Messages from SQL Server by Using the WCF Channel Model

Properties

Article ID: 2481676 - Last Review: May 31, 2011 - Revision: 2.0
APPLIES TO
  • Microsoft BizTalk Server 2009 Branch
  • Microsoft BizTalk Server 2009 Developer
  • Microsoft BizTalk Server 2009 Enterprise
  • Microsoft BizTalk Server 2009 Standard
  • Microsoft BizTalk Server 2006 R2 Branch Edition
  • Microsoft BizTalk Server 2006 R2 Developer Edition
  • Microsoft BizTalk Server 2006 R2 Enterprise Edition
  • Microsoft BizTalk Server 2006 R2 Standard Edition
  • Microsoft BizTalk Adapter for SQL
Keywords: 
kbqfe kbfix kbexpertiseadvanced kbsurveynew kbbtsadapters KB2481676

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com