One-Way XmlPolling in WCF-SQL adapter for BizTalk Receive Locations

In this post, I will share my personal experience configuring a WCF-SQL Receive location using XmlPolling. Please follow the below steps:

1. Open the BizTalk Server Administrator Console and expand your BizTalk application.

2. Right-click on the Receive Location and select “New” and then “One-Way Receive Location”. Choose your receive port and click the OK button.

3. Type a proper name for the receive location, choose the transport type as WCF-SQL, and select the Receive handler. If the WCF-SQL type is not available, please install it from the BizTalk setup and add the adapter from Platform settings. Leave the receive pipeline as XML Receive.

4. Click on the Configure button located on the right side of the transport type. In the General tab, enter the EndPoint Address, which is a unique connection setting for the SQL server.

5. Click on the Binding Tab and expand the (FOR XML) section. In the XMLStoredProcedureRootNodeName field, enter the XML root name of your schema where you want to retrieve table value in your schema tags. Then, in the XmlStoredProcedureRootNodeNamespace field, enter the target namespace from the same schema (.xsd file).

6. Repeat the same process in the Binding Tab for the General Property of Timeout value. For more information, please read my post on Configuring BizTalk WCF Timeout Values on a Binding.

7. In the Binding tab, select the XmlPolling option in the Inbound property. For the Polling(Inbound) property, set the PollingDataAvailableStatement value to either “Select 1”, “Select GetDate()”, or any other valid procedure, view or table with a condition that you want to execute during the given polling interval (PollingStatement). Also, set the PollingIntervalInseconds property to specify the polling interval in seconds.
8. Please navigate to the “Other” tab and enter your SQL Server credentials, including your username and password.
9. Leave the default settings as they are, except for the Error handling section:
  • Uncheck the “Disable location on failure” option. Doing so will ensure that the Receive Location is not disabled in case of any error. 
  • Check the “Suspend request message on failure” option. This will suspend the message in case of an error and make it resumable in the BizTalk administrator console.
  • Check the “Include exception detail in faults” option. This will ensure that if you’re using a two-way receive location in a WCF web service, the exception will be marked in fault.
10. After configuring the BizTalk WCF-SQL XMLPolling receive location, click OK twice to finalize the process. It’s crucial to map the SQL procedure and XML schema that are called in the WCF-SQL receive location using the following SQL Proc:
USE [BizTalkLive]
— =============================================
— Author:           <>
— Create date:      <02-Jul-2017>
— Description:      <Get Account Request data from requested accounts>
— =============================================
Create PROCEDURE [dbo].[procGetAccountRequestData]
    WITH XMLNAMESPACES (DEFAULT ‘http://BizTalkLive_WCF_SQL.AccountRequest’)
       SELECT AccountNumber, IFSCCode
       FROM tblAccountMaster AccountsRequest WITH(NOLOCK)
       FOR XML AUTO –, XMLDATA       

The above procedure has two main points:

i) WITH XMLNAMESPACES (DEFAULT ‘http://BizTalkLive_WCF_SQL.AccountRequest’) If you haven’t defined the default target namespace of your schema, then when you enable your WCF-SQL Receive location, it won’t map with your schema as called in the receive location, and you’ll get an error in your BizTalk console. In the SQL query, the “WITH” expression is mandatory to break the “;” (semicolon), otherwise, when you compile your procedure, you’ll receive the following error:

“Incorrect syntax near the keyword ‘with’. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.”

ii) Table name and attribute should be the same as in the XML Root/Record and Attributes/Elements, respectively, as returned in the SQL query. Please see the XML schema of the above procedure below –

“That’s it, all configuration has been completed.”

Post a comment

Leave a Comment

Scroll to Top