Since the underlying technology is very different between the two adapters – ADO.NET in the WCF-SQL Adapter and SQLXML in the old SQL Adapter – there are many differences between them.
The first difference worth mentioning is not technical but is instead related to product support. The old SQL Adapter is still available in BizTalk Server 2010. It is still used by the EDI batch functionality and also by the All.Exception send port in the ESB Toolkit 2.1. But the old SQL adapter was deprecated with BizTalk Server 2009 was released and is now unsupported in BizTalk Server 2010. Future releases of the EDI and the ESB Toolkit will most likely move to use the new WCF-SQL Adapter.
From a developer perspective quite a few things have improved. The old adapter relied on using Updategrams for update, insert and delete statements against tables and required SQL statements or stored procedures to use the FOR XML mode to return results so that the data was returned to BizTalk Server in XML format. Updategrams caused both BizTalk maps and schemas to be more complicated than necessary. And the FOR XML clause somewhat tied that stored procedure to BizTalk Server or other callers that understood the XML being returned. The new adapter does not require any of these extra steps and creates the schemas that follow the standard Insert, Update, Delete, and Select statements against tables and views and also to execute Stored Procedures and Functions.
Another area of improvement is the supported SQL Server features. The new adapter has been created to support many more features of SQL Server and overcomes many of the restraints that existed in the old adapter both in terms of performance, fine tuning and feature set. Richard Seroter compiled a comparison chart on his book SOA Capabilities in BizTalk Server 2009. The chapter can also be found online here.
The adapter pack comes with a tool called the BizTalk Adapter Pack Migration Tool. Its intent is to accelerate the adoption of the new adapters with minimal changes to you existing BizTalk solution. It accomplishes this by creating WCF-SQL versions of schemas for any old SQL adapter schemas, and also creates maps that map from the old adapter schema to the new. It is recommended if you want to take advantage of the new adapter very quickly in your existing BizTalk application. But ideally do not use the maps it creates and instead update your solution to completely remove the old adapter schemas, thus avoiding having to call two maps. For more details about the tool consult the Migration Guide for Microsoft BizTalk Adapters for Enterprise Applications available with the BizTalk Server Adapter Pack documentation here. Also see Richard’s review here.
One of the common issues developers run into after migrating schemas for stored procedures from the old SQL Adapter to the new is lack of support for generating strongly-typed schemas for stored procedures that create temporary tables. This is one limitation of the new WCF-SQL Adapter that didn’t exist in the old so let us delve into this a little bit more and cover one workaround.
Strongly-typed schemas are the most common way of receiving data from SQL Server into BizTalk solutions. The reason why the adapter fails when there are temporary tables in the stored procedure is because it uses the FMTONLY setting of SQL Server to discover the format of the stored procedure response without actually calling it. For example, when using the wizard to generate the typed polling schema for a stored procedure called uspCurrentCities it will execute the following against the database:
SET FMTONLY OFF; SET FMTONLY ON;
exec [dbo].[uspCurrentCities]
SET FMTONLY OFF;
By enabling FMTONLY before calling the stored procedure the wizard can figure out the structure of the stored procedure response without really executing it. If the stored procedure created and uses a temporary table the wizard throws an exception:
To overcome this limitation there are a few workarounds like updating your stored procedure to use table variables instead. But the most common workaround is to disable the FMTONLY setting before creating the temporary table and then re-enabling it right after. Here is an example of the uspCurrentCities stored procedure with the workaround:
USE [AdventureWorks]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[uspCurrentCities]
AS
BEGIN
SET NOCOUNT ON;
–Ensure FMTONLY OFF just before creating temporary table
DECLARE @CheckFmt bit;
SELECT @CheckFmt = 0 WHERE 1=1
IF @CheckFmt IS NULL
SET FMTONLY OFF;
–Create temporary table
IF object_id(‘tempdb.dbo.#Address’) IS NOT NULL
DROP TABLE #Address;
CREATE TABLE #Address(
[City] [nvarchar](30) NOT NULL,
[PostalCode] [nvarchar] (15) NOT NULL)
CREATE CLUSTERED INDEX IX_AddressTemp ON #Address
([City], [PostalCode]);
–Ensure FMTONLY is turned back on if it was on before
IF @CheckFmt IS NULL
SET FMTONLY ON;
INSERT INTO #Address
SELECT DISTINCT [City], [PostalCode]
FROM Person.Address
SELECT [City], [PostalCode] FROM #Address
IF object_id(‘tempdb.dbo.#Address’) IS NOT NULL
DROP TABLE #Address;
END
Generating a strongly-typed schema for this stored procedure now creates a proper WCF-SQL adapter schema:
This once deployed and configured in a BizTalk Server solution provides the expected XML content:
<TypedPolling xmlns="http://schemas.microsoft.com/Sql/2008/05/TypedPolling/FMTOnlySample">
<TypedPollingResultSet0>
<TypedPollingResultSet0>
<City>Abingdon</City>
<PostalCode>OX14 4SE</PostalCode>
</TypedPollingResultSet0>
<TypedPollingResultSet0>
<City>Albany</City>
<PostalCode>12210</PostalCode>
</TypedPollingResultSet0>
<TypedPollingResultSet0>
<City>Albany</City>
<PostalCode>97321</PostalCode>
</TypedPollingResultSet0>
…
</TypedPolling>
Interesting article.
One of the things I like about the WCF SQL adapter is the ability to turn off distributed transactions. The DTC really slows down SQL operations and a distributed transaction is not always necessary. Especially when performing read operations.
Great point Walter, thank you for the comment!
Great post Thiago. I just got caught today with another difference. The classical SQL adapter forced the SQL stored procedure author to have one select with a FOR XML clause. With the WCF-SQL adapter any SELECT statement will create a new result set. I had customer today who changed their stored procedure to do one select under one condition and another select if the first condition is not meet. This busted the whole solution because when BizTalk creates the metadata it now contains two result sets not one. As far as the customer was concerned the stored procedure was still returning the same data.
Good one Thiago. Also wanted to add the **great** property called “useAmbient” included with the new adapter 🙂 . Setting it to true ensures our SQL data gets committed into the messages box in a transaction. Moreover, this transaction level is also configurable by choosing options such as committed read, serialized, etc.
Thiago how do you map from the WCF-SQL adapter schema. I’ve use your methodology to create a solution mapping the output from the type polling WCF-SQL adapter schema and it comes up with
error btm1044: Input validation error: Could not find schema information for the element ‘http://schemas.microsoft.com/Sql/2008/05/TypedPolling/TEST_WCFSQL:City
Any ideas what this could be ???
since we have multiple temp tables, do we need to use a different variable that does what CheckFmt does in your example, or can we reuse that same variable for each table? If we can reuse it, should we SET FMTONLY ON after we’re done with working with all the temp tables, or should it still be toggled on and off before and after each table is created and populated?….please reply…Thank you in advance….VK
wonderful article i have doubt on this …
Do we get batched schema on generating it from stored procedure
Hi
Would it be correct to say that the standard BizTalk adapter pack that is shipped with BizTalk Server 2006 does not support connectivity to SQL Server 2008?
Many thanks, Ottolien