As things stand at the moment, the standard SQL adapter shipped with Microsoft BizTalk Server 2006 has been released with no support for any of the enhanced capabilities of the Microsoft SQL Server 2005 platform. The primary codebase of the adapter has been inherited from the previous version of BizTalk and although it does provide the required functionality for core integration with SQL Server through using either stored procedures or updategrams, this may not deliver the optimal performance as it would become possible if the adapter was fully optimised for SQL Server 2005.

On top of that, I will mention all those well-known limitations and uncontrollable aspects of the SQL adapter' behavior:

• A lack of support for configurable transaction isolation levels (in a pure messaging scenario);
• A requirement for a separate, often redundant adapter schema and associated, usually inefficient BizTalk map (try to stick an XML document into an attribute in the SQL adapter schema and you will see what I really mean);
• A limited support for batch operations (only with updategrams) that lacks extensibility and granularity;
• No support for user-defined database schemas in SQL 2005 (due to a security check that prohibits the fully qualified stored procedure names, e.g. [myschema].[MyProc]).

I have to admit that SQL adapter is still doing a great job simplifying the receive side of things but even here we would benefit from such value-adding features like a resultset-to-XML auto conversion that could help to avoid building dedicated FOR XML-style queries.

Maybe it is time to write my own adapter with a full understanding and appreciation of the SQL 2005 features. Let me know what you think and whether or not this crazy idea would be of any value to anyone.

Valery