Friday, January 5, 2007


Should you ever run into this warning in the eventlog, when using the SQL Adapter for BizTalk:

your problem is that the SQL Adapter is receiving a result set instead of XML.

There are two possibilities:

  1. As we all know, when using the SQL Adapter to call a stored procedure, you must write "for xml auto, xmldata" (or some variant - I like the "elements" way of things) in your Stored Procedure in order to let the schema generator generate a schema for you. After the schema has been generated, you need to remove the ", xmldata" or else schema data will be returned every time the SP is called instead of real data. Anyway, sometimes people remove too much from the SP, ie. they remove the entire "for xml auto, xmldata" instead of just ", xmldata".
  2. Sometimes, someone forgets to write "for xml auto" after a select statement in the receive location.

Both will cause the above mentioned error.

Hope this has helped someone.


Friday, January 5, 2007 11:07:27 PM (Romance Standard Time, UTC+01:00)  #    Comments [4]  | 
Saturday, January 6, 2007 3:40:44 AM (Romance Standard Time, UTC+01:00)
What I like to do is use FOR XML AUTO, ELEMENTS, XMLDATA when I generate the adapter metadata within Visual Studio, then I will remove the XMLDATA tag since we don't need the schema information once the BizTalk schema has been generated.

Of course like you said, the ELEMENTS tag is simply up to your personal preference.

Also, on SQL 2005, you have the option of using the XMLSCHEMA tag, which generates an XSD schema instead of the XDR schema generated by SQL 2000's XMLDATA tag.
Chris Romp
Tuesday, May 15, 2007 12:33:03 PM (Romance Daylight Time, UTC+02:00)
That really helped me.

Friday, November 21, 2008 11:57:45 AM (Romance Standard Time, UTC+01:00)
Hi Eliasen
Thanks for the prompt solution (I'm not using biztalk though)
Thursday, May 7, 2009 6:51:04 PM (Romance Daylight Time, UTC+02:00)
This was a quick hitter - solved my issue/oversight stat.
All comments require the approval of the site owner before being displayed.
Please login with either your OpenID above, or your details below.
Home page

Comment (Some html is allowed: a@href@title, b, blockquote@cite, em, i, strike, strong, sub, sup, u) where the @ means "attribute." For example, you can use <a href="" title=""> or <blockquote cite="Scott">.  

Enter the code shown (prevents robots):

Live Comment Preview

Theme design by Jelle Druyts