In this scenario, since we have control over the database, we can also add other usefulįields to identify the time the record was retrieved or the time we finished processing the record. When we enable the Receive Location with the parameter set at 5, we see 5 SalesOrderHeader records have been polled from the database.īecause PollingID has been set, these record are now flagged as having been retrieved by our BizTalk app. What we have are the Schemas and Binding File generated by the WCF SQL Adapter Wizard.įinally, here we see the Receive Location with the WCF SQL Adapter pointing directly to the AdventureWorks database. Selects out the rows by the uniqueidentifier.This invocation of the Stored Procedure now owns these rows.
Updates the specified number of rows with the new uniqueidentifier.Generates a new uniqueidentifier (GUID).To poll this table, we use a Stored Procedure that takes the following steps: Here is the modified table:Ī new field, PollingID of type uniqueidentifier, has been added to this table to manage the polling process from BizTalk. The example table is a copy of SalesOrderHeader, as SalesOrderHeaderPolling, from the AdventureWorks database (downloadable from CodePlex).
This pattern uses a specific field on a top level table to track which records have been polled and provide a level of concurrency management so that >1 Receive Location can be active at a time. When the database the BizTalk app is targeting is owned internally and we have influence over the database design, implementing a reliable polling pattern is a relatively straight forward proposition. While there are many ways to develop our SQL Server interfaces, in this article, I'm presenting two patterns for Polling data and a technique for retrieving multipleīizTalk: SQL Patterns for Polling and Batch Retrieve
Interacting with SQL Server is a bread-and-butter operation for BizTalk apps and native SQL Server support has been in the product since the beginning. BizTalk SQL Patterns: Polling and Batch Retrieve