Creating BizTalk WCF-SQL Adapter schemas

Make sure you create WCF-SQL adapters against the target database. Do not use stubs. A stub generates metadata that may be different.

The Microsoft BizTalk Adapter for SQL Server provides three Visual Studio components that you can use to help you develop solutions using the adapter—the Consume Adapter Service BizTalk Project Add-in, the Add Adapter Metadata Wizard, and the Add Adapter Service Reference Visual Studio Plug-in. Adapter clients must use these components to connect to SQL Server and then generate metadata for the operations they want to perform.

By using these Visual Studio components, you can:

• Browse for operations for which to retrieve metadata.
• Search for operations for which to retrieve metadata.
• Add message schemas for selected operations and port binding configuration files to a BizTalk Server project when using the Consume Adapter Service Add-in.
• Add a WCF client class or a WCF service contract (interface) for selected operations and a configuration file (app.config) to a non-BizTalk programming project when using the Add Adapter Service Reference Plug-in.

A WCF-SQL adapter first gets the metadata of a stored procedure. Finally it executes it. Consequently if the metadata cannot be created an error  occurs.  Annotating the procedure with the  FMTONLY setting solves this issue. Clients process the metadata skipping rows marked like this.

I list below some links to examples of stored procedures where metadata cannot be created until this solution is applied;

WCF-SQL Adapter with Dynamic SQL:

Simon Rivas tells us that BizTalk calls to stored procedures that contain dynamic SQL are tricky. Further he shows that simply adding FMTONLY to skip over portions of the procedure generates the metadata.

WCF-SQL Adapter with Nested Stored procedures:

Check that the stored procedure does not execute another stored procedure. Excessive nesting causes metadata errors. A store procedure that skips over the nesting for metadata generation solves this problem too. This is described here.

WCF-SQL Adapter with Temporary tables:

Annotate stored procedures containing temporary tables with FMTONLY. Marcus Rangell gives a nice explanation of what to do here. Alternatively, replace temporary tables with table variables. If you go this way beware of how you scope your variables.

In conclusion, I hope this is enough evidence to prove that you need a target database to develop WCF-SQL Adapter schemas. Furthermore a stored procedure stub will just not cut the mustard.