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).
The following properties govern how polling is configured using the SQL adapter:
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.
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.
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.
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.
Is this article also applicable for BizTalk 2010? Is polling interval issue resolved in 2010? Overall good article.
This also goes if you use the costum adapter and towards the Oracle. I've also learned that setting a ReceiveTimeout to maximum (24 days if i recall correct) was also recommended for Receving from Oracle and SQL databases.
Like Tord have said, it's important to set the Receive timeout to 24:20:31:23.647, otherwise you would encounter weird behavior like records with flag updated to different status but no message coming in BizTalk. This is happening in BizTalk 2010 as well.