WCF-SQL Adapter’s Database Schema Gotcha

• 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:

Resoluton

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.

Conclusion

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!