Microsoft Notification Services Control Utility 9.0.242.0 Could not load file or assembly 'Microsoft.SqlServer.Instapi, Version=9.0.242.0

Have you seen this error recently when attempting to configure BAM Tools as part of a BizTalk 2010 installation:

2011-02-22 13:06:04:2007 [Error] BAMTools Error configuring
Error executing bm.exe with tracing enabled. (Microsoft.BizTalk.Bam.CfgExtHelper.Utility)
Error executing process: C:\Program Files (x86)\Microsoft BizTalk Server 2010\Tracking\bm.exe (Microsoft.BizTalk.Bam.CfgExtHelper.Utility)
Microsoft (R) Business Activity Monitoring Utility Version 3.9.469.0Copyright (C) Microsoft Corporation. All rights reserved.ERROR: Failed to set up BAM database(s). There was a failure while executing nscontrol.exe. Error:"Microsoft Notification Services Control Utility 9.0.242.0╕ Microsoft Corp. All rights reserved.An error was encountered when running this command.Could not load file or assembly 'Microsoft.SqlServer.Instapi, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified." (Microsoft.BizTalk.Bam.CfgExtHelper.Utility)

This screen might be familiar to you if you have seen the above once or twice:

This message is frustrating as you need the BAM Tools configured before you can configure the BAM Portal. So even if you don't care about alerting, but you do care about BAM, you still need this to work before you can continue to the next step.

After following the Installing BizTalk Server 2010 on Windows Server 2008 R2 and 2008.docx file, I was still getting the above exception. You can download this document here: http://www.microsoft.com/downloads/en/details.aspx?FamilyID=b77d6a4f-8b41-470e-a58c-730dc5859b38&displaylang=en

The BizTalk 2010 installation document mentions that you need to install Microsoft SQL Server 2005 Notification Services if you want BAM Alerts because it is not included in Microsoft SQL Server 2008 R2 - which is the recommended SQL Server version for BizTalk 2010. You need Notification Services if you want any kind of BAM functionality - not just alerting.

As mentioned, the document recommends SQL Server 2008 R2 for BizTalk 2010 and if you want BAM functionality, you need to install SQL Server Notification services. It recommends you install 3 components that form SQL Server 2005 Notifications that can be downloaded from MSDN, they are:

1. Microsoft SQL Server Native Client (sqlncli_x64.msi)
2. Microsoft SQL Server 2005 Management Objects Collection (SQLServer_XMO_x64.msi)
3. Microsoft SQL Server Notification Services Client Components (SQLServer2005_NS_x64.msi)

So this all seems very straight forward. After installing the above components I was still getting the same error when attempting to configure BAM.

If we have a closer look at the error message, notice the program that is generating the error: C:\Program Files (x86)\Microsoft BizTalk Server 2010\Tracking\bm.exe. Also notice the assembly and assembly version bm.exe is looking for: Microsoft.SqlServer.Instapi, Version=9.0.242.0. So I looked for this dependency in the current folder (where bm is running) and couldn't see it. So it will either be on the PATH or in the GAC. Not running any tools to figure out the location where the CLR was looking (I'm lazy!), I looked in the GAC and noticed the file Microsoft.SqlServer.Instapi was present. But it wasn't the version 9.0.242.0 bm.exe was looking for. In the registry was version 10.0.0.0.

So the first thing I thought I'd try was to force the loading of the version 10.0.0.0 assembly instead of the version 9.0.242.0 version it was looking for. As bm.exe is managed code, I could create a probe that forced the load of version 10.0.0.0 by adding this to the bm.exe.config file. After doing this, on start up of the bm.exe, it overwrites the config file! so this change was completely useless. So then I thought I could add this probe to the machine.config file but thought this is getting very silly now.

The next thing I turned to my helpful friend, Google. searching hi and low for a solution to this the only concrete thing I could find was this post on the Microsoft Social forums: http://social.msdn.microsoft.com/Forums/en/sqlsmoanddmo/thread/e85cbf20-4a81-4059-88a9-6666bfeb02dc

A response from James K. Howey in the above post from Microsoft suggests its an issue with the SQL Server 2005 Management Objects Collection and this issue might be fixed in Sp2.

"I have reproduced this issue in house and can confirm that this is a problem with the Microsoft SQL Server 2005 Management Objects Collection. We will consider this issue for inclusion in Service Pack 2."

James also mentions that he tried GAC'ing the version 9 assembly but this failed with different error suggesting that the managed assembly is in fact a wrapper around something else or perhaps has other dependencies.

This was getting very messy, and all I wanted was for the BAM Portal to work - surely this is not much to ask! should it really be this hard...

So the next thing I thought was it seems BizTalk 2010 is designed to talk to SQL Server 2005 but recommends SQL Server 2008 R2. But there seems to be low-level dependencies it requires from the older SQL version. I didn't want to have to install the fully blown SQL Server 2005 on this machine so instead installed Notification Services from the fully blown SQL Server 2005 ISO.

After this I noticed the assembly Microsoft.SqlServer.Instapi version 9.0.242.0 in the GAC!!! Feeling rather upbeat, I re-executed the BAM Tools configuration (bm.exe) and it worked!:




Conclusion:

If you want BAM functionality in your BizTalk 2010 environment, do not install the Notification Services MSI's referenced in the BizTalk 2010 install guide, instead install from the fully blown SQL Server 2005 ISO from MSDN Subscriptions.

Happy BizTalk'in!