Phidiax Tech Blog

For those who have been baffled by this error when the standard set of fixes just doesn't work (aside from installing CU5, which may not directly solve it if it already is an issue in your environment), I recently took one for the team and dove head-first into the BizTalk Management Database to see where this is coming from. I have good news for those who have had this issue pop after installing or uninstalling applications prior to CU5 (or those who have an adventurous developer who may have manually deleted records from the Management Database when an uninstall didn't work :) ): this can be caused by orphaned records within certain tables in the BizTalk Management Database. With a little database research, backup, and record deletion, you can be back on track (Note that backup is in bold : always backup your management database if you plan on messing with tables directly).

ExplorerOM Library and Foreign Key Enforcement

The ExplorerOM Library is used extensively by the Admin Console for loading and displaying BizTalk information. Running a SQL trace while opening the console will give a good idea as to which tables are being loaded. Decompiling the ExplorerOM library with your favorite .NET decompilation tool will give you an even better idea of what's going on.
To summarize and avoid having to paste an entire SQL trace or decompiled library into this blog (which would not be fun for anyone), there are some foreign keys enforced by the ExplorerOM library which are NOT enforced by the Management Database. As a result, if deletions happen pre-CU5 or manually in the database, there is nothing to make sure that gets properly cascaded through any related tables to avoid this sort of problem. When the ExplorerOM library (which makes extensive use of the System.Data.Dataset and the Relations collection) loads the data and attempts to enforce the Relations in the Dataset, an exception is thrown since the data in the tables does not meet the criteria set by these relations.

List of Problem Keys

If you have this issue occur, you can start with the usual suspects. If you still don't have resolution, it may be time to check out the following foreign keys for orphans since they are enforced ONLY in the ExplorerOM library, not the database:

1. bts_orchestration.nAssemblyID -> bts_assembly.nID
2. bts_pipeline.nAssemblyID -> bts_assembly.nID
3. bts_porttype.nAssemblyID -> bts_assembly.nID
4. bts_rolelink_type.nAssemblyID -> bts_assembly.nID

For example, let's look at bts_porttype: When you examine the database table and the ExplorerOM code, you can see the added relationship for the Assembly ID in line 28 that doesn't show as a foreign key in the database table. So if you just do a quick query left-joining from the first to second table above, and limit bts_assembly.nID to null values only, you should have a list of orphans that can be verified and deleted from the left table.

Deleting Orphan Records

Before deleting any records from the management database, if at all possible, perform a full stop and shutdown of all orchestrations and hosts to minimize activity during the deletion. And as with any major change, backup the database then perform the deletion. You can then open the admin console to verify that the error is hopefully resolved and begin to bring all orchestrations and hosts back online.