Solving a Map Reference to a Deleted Schema

Recently a client got into a bit of “hot water” in their BizTalk integration environment because changes to a Common BizTalk application were not deployed prior to one of the dependent applications. This resulted in an error whenever trying to manage the environment’s applications via the BizTalk Admin Console (even when we followed the hint to refresh the console view):

Schema referenced by Map ‘Test.DependentApp.DependentSchema_to_CommonSchema’ has been deleted. The local, cached version of the BizTalk Server group configuration is out of date. You must refresh the BizTalk Server group configuration before making further changes.
(Microsoft.BizTalk.Administration.SnapIn)

The issue was that a new schema contained in the updated Common project was missing. Easy enough to understand, and the steps to resolve it would appear to be simple enough:

1. Undeploy the dependent application
2. Redeploy the Common application (using the new version)
3. Redeploy the dependent application

However, the error above thwarted us at the very first step: we could not delete the dependent application either through the Admin Console or via BTSTask!

How Did This Happen?

Our client had several dependent applications that relied on the Common BizTalk application, each one managed in its own Visual Studio solution and its own TeamCity build definition. When changes to the Common app were checked in, it was not possible to deploy to the integration environment right away because all the other dependent apps had to be manually undeployed first. But there was nothing stopping the automated redeployment of the dependent applications, which then occurred out of order. I suppose that there’s a lesson in here somewhere about managing automated builds and deployments – but that’s not within the scope of this post.

How Did We Solve It?

When it was beginning to look like a complete re-configuration was in order, my esteemed colleague Bill Chesnut came to the rescue with his in-depth knowledge of the BizTalk databases. All we needed to do was delete a couple of records in the BizTalkMgmtDb database in order to remove the invalid map reference.

As you can see in the above diagram, a table named bt_MapSpec contains all of the deployed map specifications, with references to both the incoming and outgoing schemas. Deleting the record  with the unresolved schema reference is the answer here. However, there are also two tables, bts_sendport_transform and bts_receiveport_transform, which (as you might  guess) contain references to the maps hosted in the various send & receive ports. If your map with the invalid schema reference is hosted in one or more ports, you’ll need to remove these records too. Once you’ve achieved this, you’ll be able to delete your application(s) and start over by redeploying the correct version of the common app first.

Here is a sample SQL script that can be run to get you out of trouble if you find yourself in this situation. Note that you’ll need to set two variables in order to positively distinguish the target map:

USE BizTalkMgmtDb

DECLARE @mapRecordId AS UNIQUEIDENTIFIER;
DECLARE @inDocSpecName AS NVARCHAR(256);
DECLARE @outDocSpecName AS NVARCHAR(256);

SET @inDocSpecName = ‘<insert .NET name of inbound schema here>‘;
SET @outDocSpecName = ‘<insert .NET name of outbound schema here>‘;

BEGIN TRANSACTION

— Find the map record ID
SELECT @mapRecordId = (
SELECT [id]
FROM [BizTalkMgmtDb].[dbo].[bt_MapSpec]
WHERE [indoc_docspec_name] LIKE ‘%’ + @inDocSpecName + ‘%’
AND [outdoc_docspec_name] LIKE ‘%’ + @outDocSpecName + ‘%’
)

–SELECT @mapRecordId  –uncomment this line for debugging

— Now delete from any hosting receive ports
DELETE FROM [BizTalkMgmtDb].[dbo].[bts_receiveport_transform]
WHERE [uidTransformGUID] = @mapRecordId

— … and delete from any hosting send ports
DELETE FROM [BizTalkMgmtDb].[dbo].[bts_sendport_transform]
WHERE [uidTransformGUID] = @mapRecordId

— Finally, delete the map record itself
DELETE FROM [BizTalkMgmtDb].[dbo].[bt_MapSpec]
WHERE [id] = @mapRecordId

COMMIT

You should see output similar to the following:

(0 row(s) affected)

(0 row(s) affected)

(1 row(s) affected)

The last message should always indicate one row affected, as you are only deleting one map record; the two previous will depend on the number of send and/or receive ports that host the map.

Now you should be able to browse and delete the applications in the BTS Admin Console (or via BTSTask, whichever you prefer).

Caveats

Just a couple of points & caveats about this script:

• You must specify the inbound and outbound schemas using the .NET names, and they must be specific enough to ensure that only one map record is returned. (If more than one row is returned in the scalar query the script will throw an error anyway.)
• This method should never be used in production as Microsoft will undoubtedly not support any ill effects from using this approach!