Share via

Typed Polling with WCF-SQL Adapter: Best Practices and Troubleshooting Tips

  • Article

Introduction

The [[WCF]]-based SQL adapter available with the BizTalk Adapter Pack enables adapter clients to receive data-change messages from the SQL Server database. The SQL adapter supports receiving "polling-based" messages wherein the adapter executes a specified SQL statement (SELECT statement or stored procedure), retrieves or updates the data, and provides the result to the adapter client at regular intervals of time. The SQL adapter supports three types of polling: weakly-typed Polling (also called Polling), strongly-typed Polling (also called TypedPolling), and polling using statements or procedures that include a FOR XML clause (also called XmlPolling). For more information about the different types of polling, see Polling SQL Server by Using the SQL Adapter with BizTalk Server (http://go.microsoft.com/fwlink/?LinkId=221195).

In this topic, we discuss about some common misconceptions related to typed polling, discuss in detail about the binding properties that are used to configure typed polling, best practices, and troubleshooting tips. This topic does not talk about how to configure typed polling. For instructions on how to configure typed polling, see Receiving Strongly-typed Polling-based Data-changed Messages from SQL Server Using BizTalk Server (http://go.microsoft.com/fwlink/?LinkId=221198).

Binding Properties for Configuring Polling

The following properties govern how polling is configured using the SQL adapter: 

Binding Property  Description 
InboundOperationType Specifies whether you want to perform Polling, TypedPolling, or Notification inbound operation. Default is Polling. To receive strongly-typed polling messages, set this to TypedPolling.
PolledDataAvailableStatement Specifies the SQL statement that the adapter executes to determine whether any data is available for polling. The SQL statement must return a result set consisting of rows and columns. Only if a row is available, the SQL statement specified for the PollingStatement binding property will be executed.
PollingIntervalInSeconds Specifies the interval, in seconds, at which the SQL adapter executes the statement specified for the PolledDataAvailableStatement binding property. The default is 30 seconds. The polling interval determines the time interval between successive polls. If the statement is executed within the specified interval, the adapter waits for the remaining time in the interval.
PollingStatement Specifies the SQL statement to poll the SQL Server database table. You can specify a simple SELECT statement or a stored procedure for the polling statement. The default is null. You must specify a value for PollingStatement to enable polling. The polling statement is executed only if there is data available for polling, which is determined by the PolledDataAvailableStatement binding property. You can specify any number of SQL statements separated by a semi-colon.

Important: For TypedPolling, you must specify this binding property before generating metadata.
PollWhileDataFound Specifies whether the SQL adapter ignores the polling interval and continuously executes the SQL statement specified for the PolledDataAvailableStatement binding property, if data is available in the table being polled. If no data is available in the table, the adapter reverts to execute the SQL statement at the specified polling interval. Default is false.

How These Properties are Used?

The following diagrams show how the properties are put to use by the SQL adapter.

This diagram shows the polling algorithm when the value of PollWhileDataFound is set to “False” (default setting).

This diagram shows the polling algorithm when the value of PollWhileDataFound is set to “True”.

 

The UseAmbientTransaction binding property specifies whether the SQL adapter performs the operations using the transactional context provided by the caller application. Default is true, which means that the adapter always performs the operations in a transactional context. If there are other resources participating in the transaction, and SQL Server also joins the transaction, the transaction gets elevated to an MSDTC transaction. The value you set for this binding property also affects how the properties related to polling function.

  • If UseAmbientTransaction = True. PollDataAvailableStatement is executed. If PollDataAvailableStatement returns some data, then PollingStatement is executed. If PollingStatement fetches some data, it is returned back to the calling application.
  • If UseAmbientTransaction = False. PollDataAvailableStatement is not executed. PollingStatement is executed, and if the statement fetches any data, it is returned back to calling application.

The way PollDataAvailableStatement and PollingStatement ascertain whether data is available is also different. For PollDataAvailableStatement, data is available if the return value is greater than zero. For PollingStatement data is available if a data set is returned from SQL containing rows greater than zero.

Timeout Issues with Polling

In BizTalk Server 2006 R2 and BizTalk Server 2009, if the polling interval is set to a very small value, you might notice that at random intervals the application stops processing records and places a lock on the database. Here’s why: On the receive side, the adapter has a binding property, ReceiveTimeout, which is set to 10 minutes by default. If no data is polled during this period, the adapter starts a new thread and allocates more memory, without releasing old resources. So, if the adapter does not poll any data for a while, a new thread is created in the host instance every 10 minutes until it reaches the threshold, which is 1000. Once the threshold is hit, no threads are left for this host instance and hence it fails to process any tasks.

Microsoft releases a hotfix 2300507 to resolve this issue.

See Also

Another important place to find a huge amount of BizTalk related articles is the TechNet Wiki itself. The best entry point is BizTalk Server Resources on the TechNet Wiki.