The difference between WCF-SQL adapter and Sql Ada

Question

I am new to BizTalk development. I am using BizTalk server 2010. I am doing a migration existing code which use BizTalk 2006 to 2010. In the old application we have used Sql adapter to get procedure out put for a xml file. MSDN for the 2010 specifies that

BizTalk Server no longer supports the SQL Adapter. Consider using the Microsoft BizTalk Adapter Pack to achieve the same functionality. (http://msdn.microsoft.com/en-us/library/aa578608.aspx)

But when I install BizTalk 2010 it has the SQL adapter.And they said that WCF_SQL adapter can use instead of SQl adapter. I want to know the difference between those two adapters. I found some blogs. But those wan't help me. If anybody knows please share it

Thanks

Answer 1

SQL Adapter has been available in BizTalk forever (not sure which version it came in with but since at least 2004). It works but it is very quirky to work with (for example, it wraps everything in an envelope mesage by default).

WCF SQL Adapter was designed to work with the latest versions of SQL server which have native support for XML as a sql type. It provides much better functionality and is much nicer to work with.

So there's good reason why you shouldn't use the SQL adapter any more.

Answer 2

The SQL Adapter is deprecated, meaning it will be removed from the BizTalk product base at some future version.

Functionally, the WCF-SQL adapter supports the majority of native types, both input and output, to Sql stored procedures, and is able to handle return data-sets without them explicitly converted to XML using the FOR XML, [AUTO|EXPLICIT] clause.

This last point simplifies the development effort required, and allows your team to use standard T-SQL stored procedure practices.