Step by step configuration One Way XmlPolling WCF-SQL adapter in BizTalk Receive Locations

In my this post I am going to share my personal experience to configuration WCF-SQL Receive location using XmlPolling, Please go through with following steps:

1. Open BizTalk Server Administrator Console and Expand your BizTalk application

2. Right Click on Receive Location>New>One-Way Receive Location and Choose your receive port then click OK button.

3. Type proper name of receive location then choose transport type as WCF-SQL (If there is no WCF-SQL type, please install it form BizTalk setup and add adapter from Platform settings) and select Receive handler for it and leave receive pipeline as XML Receive.

4. Click on Configure... button from right side of transport type

5. In General tab type EndPoint Address - connection setting of SQL server (It should be unique)

6. Now Click Binding Tab then expand (FOR XML) and in XMLStoredProcedureRootNodeName: type xml root name of your schema where you want to retrieve table value in your schema tags after that in XmlStoredProcedureRootNodeNamespace: type Target Namespace form the same schema(.xsd file).

7. Continue same in Binding Tab for General Property of Timeout value pleaser read my post - Configuring BizTalk WCF Timeout Values on a Binding

8. Continue same in Binding Tab in Inbound property select XmlPolling and from Polling(Inbound) property value of PollingDataAvailableStatement: Select 1 or Select GetDate() or any procedure or select any view or select with any table with condition when you want to execute Polling statement (PollingStatement) procedure in given polling interval with property from  PollingIntervalInseconds

9. Go to Other tab set your SQL Server credential User name and Password

10. Now in Messaging tab leave default setting except Error handling section:

 i. Uncheck Disable location on failure because if any error occur then Receive Location would be disable

 ii. Check Suspend request message on failure so that if in case of error message is suspend and  resumable  in BizTalk administrator console

iii. check Include exception detail in faults so if you are using two-way receive location in wcf web service then exception marked in fault.

11. Finally click two times OK and all BizTalk WCF-SQL XMLPolling receive location configuration is done.

12. Now very important to map SQL procedure and xml schema i.e called in WCF-SQL receive location with the help of below following SQL Proc:

USE [BizTalkLive]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:           <Jitendra Yadav from BizTalkLive>

-- Create date: <02-Jul-2017>

-- Description:      <Get Account Request data from requested accounts>

-- =============================================

Create PROCEDURE [dbo].[procGetAccountRequestData]

AS

BEGIN

       SET NOCOUNT ON;

    WITH XMLNAMESPACES (DEFAULT 'http://BizTalkLive_WCF_SQL.AccountRequest')

       SELECT AccountNumber, IFSCCode

       FROM tblAccountMaster AccountsRequest WITH(NOLOCK)

       FOR XML AUTO --, XMLDATA

          

END

In above procedure there are two main point i.e.

 i) WITH XMLNAMESPACES (DEFAULT 'http://BizTalkLive_WCF_SQL.AccountRequest')

If you are not define your default target namespace of your schema then when you enable your WCF-SQL Receive location, It does not map with your schema as call in receive location and you get error in your BizTalk console. In SQL Query "WITH" expression is mandatory to break ";"(Semicolon) otherwise when you complied your procedure then definitely receive 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 same as in xml Root/Record and Attributes/Elements respectively as return in SQL query. Pl see below xml schema of above procedure-

Thats it all configuration has completed.

!! Keep visiting BizTalkLive !!