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.
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.