Wednesday, July 19, 2006

BizTalk 2006 SQL Schema Generation Wizard Error


Today I got this strange error when I was trying to use the SQL Transport Schema Generation Wizard. The error is "Failed to execute SQL Statement. Please ensure that the supplied syntax is correct." I was trying to call a stored proc that runs a simple SELECT statement. The stored proc worked fine elsewhere. What was going on? Well it turns out that you have to add some magic words at the end of your stored proc for this to work. Just paste the following at the end of your SQL code:

for xml auto, xmldata

I found this tidbit of info on this blog

4 comments:

Yousef T. Omar said...

Hello,
Check out my blog
yousefo.blogspot.com
Cheers

jagadeesan said...

Thanks ya

Zsanett said...

What if you DO NOT want to use "for xml auto, xmldata"
because you need to format your output differently using for xml path??? What then? I only have a hackish workaround to flatten my table and schema and then use xml auto but i really did not want to do that, that is why xml path exist...

Developer42 said...

Hey Joe,
thanks for the post; helped me get to the root of my issue.
For anyone wanting to use a select statement, with columns which don't come from tables, use the "xml raw" statment instead of "xml auto".
e.g.
SELECT 1 AS Trigger FOR XML RAW
Cheers,
JB