Multiple ways to Poll (Receive Location in BizTalk) in the SQL Adapter

  • Article

In this post, I outlined the ways in which stored procedures can be executed using the SQL Adapter. Similar to the methods described there, are very similar methods for polling on the inbound side. (I won’t mention the advantages/limitations/workarounds here, since there are very similar to those mentioned in the other post).

1. The first method for polling, manifested as the “Polling” operation in the Metadata Search/Browse tool. At runtime, the SQL statement specified in the “PollingStatement” binding property gets executed, and the result sets returned (we support multiple result sets being returned) are surfaced as System.Data.DataSet[] (an array of datasets).

NOTE – in order to let the adapter know that you want it to perform this operation at runtime, set the InboundOperationType binding property to Polling.

2. The next approach was to try and expose the result sets as “strongly typed”. For this purpose, we added the “TypedPolling” operation which you can see in the Metadata UI. Metadata is obtained (at design time + runtime) using ADO.NET’s CommandBehavior::SchemaOnly option (which translates to SET FMTONLY ON).

NOTE – in order to generate the metadata, setting the InboundId property in the Connection Uri is mandatory – since in a single BizTalk application, you can have multiple receive locations with different PollingStatement values, and in order to differentiate the XSDs, the InboundId value is used in the XSD namespace (thus, the InboundId should be different for different Receive Locations)

NOTE – in order to let the adapter know that you want it to perform this operation at runtime, set the InboundOperationType binding property to TypedPolling.

3. In CTP4 (releasing end of October 2008), we’ve added one more mechanism. This was mainly done for backward compatibility.

In this method, the PollingStatement you specify should return data in XML format (e.g., SELECT Col1 FROM Table1 FOR XML AUTO). At runtime, the XML data returned is enclosed within the root tag defined by the values in the binding properties XmlStoredProcedureRootNodeName and XmlStoredProcedureRootNodeNamespace.

NOTE – in order to let the adapter know that you want it to perform this operation at runtime, set the InboundOperationType binding property to XmlPolling.