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, an enterprise software that acts as an all-in-one solution for better administration, operation, support and monitoring of Microsoft BizTalk Server environments.

One Platform Operations, Monitoring and Analytics Software
BizTalk360

microsoft biztalk

Learn more

Over 500 customers across 30+ countries depend on BizTalk360

ServiceBus360

Azure service bus

Learn more

Start managing your Azure Service Bus namespaces in minutes

One Platform - Operations, Monitoring and Analytics Software
BizTalk360

microsoft biztalk

Learn more

Over 500 customers across 30+ countries depend on BizTalk360

One Platform - Operations, Monitoring and Analytics Software
ServiceBus360

Azure service bus

Learn more

Start managing your Azure Service Bus namespaces in minutes

Back to Top