Archive for November, 2009

WCF-SQL Adapter’s Database Schema Gotcha

On a recent project I was working with BizTalk 2009 and SQL 2008 and the customer was interested in moving some database objects to a new SQL database schema (for example, from dbo to MySchema). In this post, I refer to types of schemas: BizTalk schemas (XSDs) and database schemas (SQL security mechanism).
I had been interacting with these database objects using the WCF-SQL adapter from the BizTalk Adapter pack. Typically, BizTalk’s port abstraction will take out all description of the physical connectivity details and provide you with a connection independent message type. Connectivity details are stored in the port properties and persisted as binding information. Through the use of the Adapter pack wizard, BizTalk schemas are created for interacting with various adapter data sources such as with the WCF-SQL adapter and SQL databases.
Initial Observations
So initially I expected the database schema change would not be a problem because the database object details were all specified as properties of the port. I modified the following port properties to point to the new schema, expecting this to work:
  • BizTalk WCF-Custom SOAP Action Headers such as TypedProcedure/dbo/MyProcCall to TypedProcedure/MySchema/MyProcCall
  • BizTalk binding properties with SQL information such as the properties notificationStatement, pollingStatement, or polledDataAvailableStatement, etc.

I restarted my BizTalk application and hosts and I started getting errors that referred to my old database schema. At this point I realized that the database schema is actually hard coded during the BizTalk schema generation process. This is a significant gotcha; that the changing of the database schema for custom objects that BizTalk uses requires a regeneration of the BizTalk schemas. The next section describes the steps I went through to correct the old database schema references after moving my database objects to the new database schema:


In addition to the deployment steps done above, I had to update several of my BizTalk solution artifacts. For my BizTalk solution, I was working with 5 different database objects and I did not want to delete all of my BizTalk schema files (approx. 15 different files) and go through the adapter wizard so many times. I decided to opt for a find and replace solution instead. Here are the steps I did to replace the old database schema references:

  • Open up the base and referenced BizTalk schemas using the Open With…, XML Editor. If you are interacting with rows of data then you may have separate referenced schema files that may also have the old schema in them.
  • Do find/replace and search for "/dbo" or whatever old database schema you are replacing. The "/" comes from the SOAP Action Header.
  • Save the schema changes.
  • Once you replace one database schema reference, your BizTalk schema will be in an inconsistent state until all of the database schema references have been updated. Try opening the BizTalk schema in the BizTalk schema designer and if you get a warning when opening you will know that there are still old database schema references to migrate. When all of the old database schema references are replaced you will no longer get a warning
  • Recompile
  • Then investigate the BizTalk map and orchestration files because these also contain hard coded references to the old database schema.
  • It would seem that a recompile of your BizTalk solution would regenerate the .cs files for the BizTalk maps and orchestrations, but I found this to not be the case. An approach I found that worked was to open any BizTalk maps and make an insignificant change (add a functoid and then remove it) just to change the file status to edited and then to save the map. This would refresh the generated map code.
  • Also, I opened all of my BizTalk orchestrations and double-clicked all of the Transform shapes and then checked the button "Open the map when I click the OK button" to change the file status to edited. This refreshed the generated orchestration code. Even though no actual changes were made the generated code was refreshed.
  • You could alternately remove all of the generated files such as those named like *.odx.cs or *.btm.cs
  • One disadvantage of this find & replace technique is that your old generated binding files will still refer to the old database schema so be sure to discard those.


This post has shown that a change to the database schema for SQL objects can have a major ripple effect on a BizTalk solution that uses the WCF-SQL adapter. I do not expect that changes to database schemas occurs very often so you may never encounter this but if you do, be prepared for a little bit of work to get the updated database schema names back into your solution. Thanks for reading!

, ,

1 Comment

Configuring BAM Tools on W2K8 R2


To continue my tradition of trying BizTalk in unsupported (or not fully supported) environments, I have been working with BizTalk 2009 on Windows Server 2008 R2 on 64-bit. This OS is being used at my current client and I have been able to configure everything in BizTalk to work except for the BAM Tools / BAM Alerts functionality.

I had posted on setting up BAM with SQL 2008 a few months ago and once the 4 hotfixes were released, I was able to get everything working on a single machine after applying the hotfixes and reconfiguring BizTalk. The current release of the BizTalk install/setup guides only go so far as supporting W2K8 R1 but do not officially announce support for R2. This is due to the way the dates lined up around the BizTalk 2009 release, but I always wonder if there are any issues, especially breaking ones.

This post walks you through a hurdle I encountered configuring BAM Tools in a multi-server environment.

System Configuration

Here is the configuration I am trying now:

  • W2K8 R2
  • SQL 2008 on separate server
  • VS 2008 w/ SP1
  • 4 Hotfixes for Notification Services, etc. (64-bit version)
  • BizTalk 2009

Configuration Issues

After applying the 4 hotfixes I try to configure BAM Tools. I am getting the following error:

Microsoft SQL Server 2008 Data Transformation Services (DTS) for BAM Archiving is not installed on the local machine.  Please install Microsoft SQL Server 2008 Integration Services. (Microsoft.BizTalk.Bam.CfgExtHelper.ToolsHelper)
Could not load file or assembly ‘Microsoft.SqlServer.ManagedDTS, Version=, Culture=neutral, PublicKeyToken=89845dcd8080cc91’ or one of its dependencies. The system cannot find the file specified. (Microsoft.BizTalk.Bam.CfgExtHelper)

This error looks similar to the one I had back on October 30, 2008 in this post:!62E68922E47BC425!400.entry. With the changes to SQL 2008, the workstation tools were moved into a different package component known as the Management Tools. The similar error was resolved with SQL 2005 by installing the Workstation Tools on the BizTalk server. I was able to resolve the above error by checking the Management Tools (Basic & Complete) SQL 2008 features as shown in the screenshot:


As software products change, their names and subsystems also change. But the documentation for the products usually changes slower so there is often a small gap to watch out for. I found one when configuring BAM Tools for BizTalk 2009 with SQL 2008 on Windows Server 2008 R2. This post can also serve as some of the scant evidence that BizTalk 2009 runs successully on Windows Server 2008 R2, but the issue described above might be one of the supportability gaps.


, ,


BizTalk 2009 on Visual Studio 2010 Beta 2

If you saw my posts a few months ago, I was testing BizTalk 2009 compatibility with VS 2010. My earlier posts found that the BizTalk extensions would only load in VS 2008 and not in VS 2010. In this post, I do a refresh of the install experience and give some preview of the .NET 4 features showing up in BizTalk 2009. The same conclusion about compatibility is reached here: the BizTalk 2009 extensions do not work with VS 2010. There is a new workarounds for getting a VS 2008/VS 2010 environment for working with BizTalk 2009.
Application Install Order
  • Use a clean VPC image
  • Setup pre-requisites: W2K8, SQL 2008
  • Install VS 2010 Beta 2
  • Install VS 2008, and VS 2008 SP1
  • Install BizTalk 2009

BizTalk Configuration

Then when configuring BizTalk on the first step for Enterprise Single Sign-On I get an error similar to this (cannot remember exact wording sorry):
Cannot connect to SSODB (Win32)
I found this blog post about re-regasm-ing the SSOSQL assembly: This helped me get around the error when configuring SSO. It is interesting that the problem occurs when VS 2010 is install prior to or after BizTalk 2009. I was then able to go through the BizTalk configuration wizard successfully. I was able to create BizTalk projects in VS 2008 fine. I did open the VS 2010 extensions manager and check for the BizTalk extensions but they are not showing up there.
There are quite a few valuable improvements with WCF 4 such as WS Discovery support, routing, and additional changes to Windows Workflow. All of these will have an impact on the Connected System landscape. Enjoy!

, ,