SQL Adapter changes in BTS 2006!

This is been an old post lying in my drafts…apologies if it is slightly not in sync.

While migrating our applications from BizTalk Server 2004 to BizTalk Server 2006, we came across a weird error when we were trying to debatch a message received from sql server 2000/2005:

“Unexpected event (“document_start”) in state “processing_empty_document”.”

This error occurs due to the changes made in the SQL Adapter receive pipeline in BizTalk Server 2006. I jumped to investigate the problem and the solution and here it goes as below:

Problem:The SQL Adapter pipeline is not able to read the header and footer tags of xml attributes when the stored procedure is passing xml data using XML AUTO. In BizTalk Server 2004, the SQL Adapter schema was written in such a way that it was compatible with AUTO feature. But, in SQL Server 2005 has enabled batching and each line is executed.

Solution(s): Inorder to resolve this issue, we either need to make changes to the existing source schema, where we add the tag names to each tag in the xml file. For example: you have an xml message as below:

a. Modifications to source schema –  Change the source schema to include tags for each element. for example <tag1> value</tag1>

b. Modifications to stored procedures –  Replace XML AUTO with XML EXPLICIT.