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.

SchemaMissing_Datamodel

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!

About Dan Toomey
Husband, father, Enterprise integration geek, Pluralsight author, Brisbane Azure User Group leader (@BrisbaneAzureUG), MCPD, MCT, MCTS & former professional musician.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

John Glisson - Geek of the Cloth

Thoughts on integration, technology and what-not...

prashantbiztalkblogs

My BizTalk Experiences

The CRUCIBLE

THINK: It's not illegal....yet.....

paulbouwer.com

life and technology

Abdul Rafay's BizTalk Blog

My experiences with BizTalk related to architecture, development and performance in my enterprise.

Mike Diiorio

Connected Systems and other thoughts

BizTalk musings

Issues, patterns and useful tips for BizTalk development

EAI Guy.net

Enterprise Applicaiton Integration and SOA 2.0

Connected Pawns

Mainly BizTalk & Little Chess

Man Vs. Machine

Why can't we all just get along?

Adventures inside the Message Box

BizTalk, Azure, and other tools in the Microsoft stack - Johann Cooper

Biz(Talk)2

Talk, talk and more talk about BizTalk

Richard Seroter's Architecture Musings

Blog Featuring Code, Thoughts, and Experiences with Software and Services

Sandro Pereira BizTalk Blog

My notes about BizTalk Server 2004, 2006, 2006 R2, 2009, 2010, 2013 and now also Windows Azure BizTalk Services.

BizTalk Events

Calendar of BizTalk events all over the world!

Mind Over Messaging

Musings on BizTalk, Azure, WCF, and Enterprise Integration

The WordPress.com Blog

The latest news on WordPress.com and the WordPress community.

%d bloggers like this: