Using Sql Adapter in Biztalk 2010

Q:

Anyone every actually pull off a successful FORXML polling query using the WCF-SQL Adapter?  The documentation on MSDN for this does not seem to show a clear way to do it.  Every example is a trivial 'this is so easy with a simple single table not using the classic SQL adpter' and I'm at my wits end with it. 

Something that takes 30 seconds with the old adapter is taking all day and not working... and involving notepad according to MSDN. 

Ans:

OK, I got the answer to this one!!! It seems the documentation is behind on how AWESOME BizTalk is. So I read that the classic SQL adapter is included in BizTalk 2010, but not supported and I couldn't figure out why it was still there. I was just going to keep the old solution, but since this is for my book I thought I'd try to figure out the right way. I'll be honest, I was really upset earlier if you couldn't tell by my tone after trying to work through this: http://msdn.microsoft.com/en-us/library/dd787898.aspx

So you just run the old wizard, like you used to, then you simply deploy the schema and create a receive location via the admin console. Then you just set the type to XmlPolling and put in "exec MyProcName" and the where clause from your existing FORXML stored procedure in a stripped down query in PolledDataAvailableStatement. Like magic it just all works!

I am writing a blog post about this now, but this deserves to be better documented.

Oh one last thing... have to change the Element Form Default from qualified (as it was in the old SQL Adapter) to unqualified.

So NO we did NOT break this!  Now it works better!