BizTalk SQL Adapter woes
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.