]> BizTalk SQL Adapter woes 🌐:aligrant.com

BizTalk SQL Adapter woes

Alastair Grant | Mon 12 Mar 2007

I've been having hell trying to get a request-response message working with a SQL Adapter, executing a Stored Procedure from BizTalk 2006.

I have a proc that takes variables, and returns a recordset. I have been able to generate the adapter to this without a problem using the "Add Generated Items" wizard. I have been able to successfully use the mapper to map data from a custom schema to the request part of the message (which has the SP variables).

This executes fine and returns data into BizTalk (I know, I can see it in the suspended messages). But when I try and map data from this response message into a new schema it simply failed to find any data.

It is as-if there was a problem with the X-Path and it was unable to locate the elements, as a new document was being created, just only the root element.

After much searching and reading I was lead down the route of creating an Envelope schema, which involves having a schema wrapped inside another one which can understand the data better... or something.

This was fruitless and really rather annoying to have to use. Then I found by luck somebody mentioning to remove the "XMLDATA" line from your procedure.

To create a SP that BizTalk will recognise when it's being imported you have to add the following on the end:

FOR xml auto, xmldata

The trick though, is to delete the "xmldata" part before you come to use it; as this sends back schema information on the result set; which is not what you want during runtime.

So, I removed it, built it, deployed it, ran it... worked a charm.

I hope this is useful for somebody.

Breaking from the voyeuristic norms of the Internet, any comments can be made in private by contacting me.