BizTalk SQL Adapter and database schemas

When using the SQL Adapter in BizTalk 2006 to Add Generated Items from the adapter; you might come into a problem is you're using different database schemas with your stored procedures.

The wizard will display all procs irrespective of the database schema they are in. It does not display the schema name (which can lead to confusion). To make matters much worse, when it runs the proc to get the the output schema (XMLDATA); it will fail to get the response due to not finding the proc under the default schema for the configured user.

It's worth noting that you cannot specify the schema on a Send Port either, so even if you tap in all the XSD information by hand, you still wouldn't be able to run the proc from BizTalk successfully.

There are two obvious workarounds:

1. Create your procs under your default schema (e.g. "dbo")
2. Create a new user logon to be used for these schemas and assign your schema as the default to this user in SQL.