Recently a colleague of mine and I spent a significant amount of time trying to use the SQL Adapter wizard in BizTalk 2006 to generate schemas corresponding to views in SQL 2005 that were really views on data that exists in a Oracle database seen in SQL as a linked server. Ok, that's a mouthful so let me chop that up a bit.
1) Oracle database has data in table A
2) SQL 2005 has a linked server defined which points to the Oracle database.
3) View VA in SQL 2005 uses OPENQUERY to get data from Oracle table A
4) Stored Procedure SPA does a select * from VA
5) BizTalk SQL Adapter Wizard in Visual Studio 2005 tries to create a schema for data coming from SPA.
6) BizTalk SQL Adapter Wizard returns back error saying 'Failed to execute SQL Statement. Please ensure that the supplied syntax is correct.'
Now I have seen this error message before, and if you look back a few posts you'll see that there are some keywords (for xml auto, xmldata) that need to be placed at the end of a stored proc for the wizard to work. This wasn't the problem.
After many days of hunting things down, we realized that our problem lay in the DTC settings between the SQL server and the Oracle server. We could run the wizard against local SQL tables just fine, but the linked stuff would fail. Using the SQL profiler helped here as well, since we could see a much better error message than the one stated above. We then tried to turn off DTC between our SQL box and the oracle box, but this didn't seem to be helping. Actually it wasn't working is what we realized. It seems that you can't turn off DTC if you are using the MS OLE DB provider for Oracle.
Our solution ended ended up being the installation of the Oracle OLE DB provider for Oracle (ie the one downloaded from Oracle.com) and then turning off the DTC on that. This magic is achieved by dropping DistribTX=0 in the provider string for the linked server. Now we can use the wizard to our heart's content.
There are plenty of gotchyas to deal with when setting up this scenario but most are well documented. This one wasn't so enjoy.