SQL Adapter and Debatching. Beware when you process a message that contains consecutive empty elements, debatching will fail.

|  Posted: February 21, 2007  |  Categories: BizTalk Server

For how to configure SQL Adapter and Debatching see the article writtin by Richard Seroter

This problem occurs when you process a message that uses a document structure that resembles the following.

<DDLService xmlns=”http://www.digitaldeposit.net/schemas/SQL”>

<CustomerActivationMessages AccountNumber=”12345” DDLServiceType=”ORDERRESPONSE” DDLServiceName=”ORDERRESPONSE” CompanyName=”XYZ” CountryCode=”GB” />

<CustomerActivationMessages AccountNumber=”98765” DDLServiceType=”ORDERRESPONSE” DDLServiceName=”ORDERRESPONSE” CompanyName=”ABC” CountryCode=”GB” />

</DDLService >

Envelope Schema: DDLService

Document Schema: CustomerActivationMessages

In our solution, SQL Adapter submits the above message via XmlReceive (used for debatching) pipeline into Biztalk. As soon as the SQL adapter polling starts we started to see the following exception message:

There was a failure executing the receive pipeline: “Microsoft.BizTalk.DefaultPipelines.XMLReceive, Microsoft.BizTalk.DefaultPipelines, Version=3.0.1.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35” Source: “Pipeline ” Receive Port: “Receive.Internal.SQL.1Way.AllPollingService.ActivationMessages” URI: “SQL://SERVER/SchOrderPolling/ORDERRESPONSE/ORDERRESPONSE/902971” Reason: Unexpected event (“document_start”) in state “processing_empty_document“.

Little bit of search revealed its a known issue with Biztalk 2006 (See the KB Article http://support.microsoft.com/kb/927741) and it applies to any message which contains consecutive empty elements as shown in the beginning of the article. If you notice the message carefully, you can see there is only root element with few attributes and no content element(s).

You got few options to resolve this problem, if you are flexible with the schemas you can add ELEMENTS along with FOR XML AUTO in your SQL Query as shown below

FOR XML AUTO, ELEMENTS

which will result in following xml

<DDLService xmlns=”http://www.digitaldeposit.net/schemas/SQL”>

<CustomerActivationMessages>
<AccountNumber>12345</AccountNumber>
<DDLServiceType>ORDERRESPONSE</DDLServiceType>
<DDLServiceName>ORDERRESPONSE</DDLServiceName>
<CompanyName>XYZ</CompanyName>
<CountryCode>GB</CountryCode>
</CustomerActivationMessages>

<CustomerActivationMessages>
<AccountNumber>98765</AccountNumber>
<DDLServiceType>ORDERRESPONSE</DDLServiceType>
<DDLServiceName>ORDERRESPONSE</DDLServiceName>
<CompanyName>ABC</CompanyName>
<CountryCode>GB</CountryCode>
</CustomerActivationMessages>

</DDLService >

or, there is a hot fix available from Microsoft at http://support.microsoft.com/kb/927741.

Nandri!

Saravana

Author: Saravana Kumar

Saravana Kumar is the Founder and CTO of BizTalk360 | Serverless360 | Atomic Scope | Document360. Founder of multiple B2B products that solve the pain point of enterprise customers. Entrepreneur, Investor and Technical enthusiast. Passionate about running and scaling a company in a profitable way with calculated risks.

Back to Top