Streaming not supported over multiple column result

by eliasen 5. January 2007 22:07

Hi

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.

--
eliasen

Tags:

BizTalk 2006

Comments (4) -

Chris Romp
Chris Romp
1/6/2007 2:40:44 AM #

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.

Reply

Shankar
Shankar
5/15/2007 10:33:03 AM #

That really helped me.

Thanks

Reply

Rama
Rama
11/21/2008 10:57:45 AM #

Hi Eliasen
Thanks for the prompt solution (I'm not using biztalk though)

Reply

Alex Brown
Alex Brown
5/7/2009 4:51:04 PM #

This was a quick hitter - solved my issue/oversight stat.
Thanks!

Reply

Add comment

  Country flag

biuquote
  • Comment
  • Preview
Loading

About the author

Jan Eliasen is 37 years old, divorced and has 2 sons, Andreas (July 2004) and Emil (July 2006).

Jan has a masters degree in computer science and is currently employed at Logica Denmark as an IT architect.

Jan is a 6 times Microsoft MVP in BizTalk Server (not currently an MVP) and proud co-author of the BizTalk 2010 Unleashed book.

BizTalk Server 2010 Unleashed


Buy from Amazon

Microsoft MVP


6 times: July 2004, July 2008, July 2009, July 2010, July 2011, and July 2012. Not currently an MVP.

MCTS

Image to show

Month List

Page List