Fix for BAM error "Views or Activites may be missing because one or more database(s) could not be contacted"

I had to migrate a BAM implementation into production from a development environment where everything in localized to a clustered database back end.

The deployment went fine, including the BAM part, but I couldn't access the views in the BAM portal. I verified that data was being written into the BAM database, but when I went to the portal to view the data I saw the following message:

Views or Activites may be missing because one or more database(s) could not be contacted. No view to display

There was also an error in the event log which read:

Referenced database 'BAMPrimaryImport' on server 'xxxx' is not accessible. The error is:
System.Data.SqlTypes.SqlNullValueException: Data is Null. This method or property cannot be called on Null values.

After some investigation I learned that the owner of the BAMPrimaryImport database needed to be a domain account. I tried to change the account role:

sp_addrolemember 'dbowner' 'domain\user'

This failed with an error.

So I ran instead (which does not remove the user, just ‘resets’ the users access rights):

sp_revokedbaccess 'domain\user'

Which threw an error, but the account was holding the db owner schema, so I changed that to dbo and it worked.

Then, I could run the original call (which now worked):

sp_changedbowner 'domain\user'

The views are now visible in the portal, so if you see this error above, this may be the cause.

How to Recover the XML for an Acivity

Recently I created an activity, then saved off the XML from Excel to a folder (let’s call it v1) to deploy using the command like tool BM.exe.

I needed to make significant changes to the activity, so significant in fact, that I needed to re-create the activity entirely, so I made a new activity (call it v2) and made the mistake of overwriting the v1 XML file. When I wanted to remove the previous (v1) activity, I realized that I had no way to remove it because BM.exe requires the use of the saved off XML file.

Being fairly new to BAM, I wondered, how do I remove the v1 activity?

Well, there is a way to recover the XML from the v1 activity.

From the command line, execute:

bm.exe get-defxml -FileName:BAMDefinition.xml

That command recovers the configuration from the BAM DB which can then be leveraged to delete the v1 version of the activity.

Note: You must edit the XML to maintain the activities you want, if you exec the remove without editing the XML first you remove ALL your activities in the DB.

Then you can then execute:

bm.exe remove-all -DefinitionFile: BAMDefinition.xml

You should never delete anything surrounding BAM by hand, this will cause major problem if you do.

BAM: The Case of the Missing Pivot Table Data

I had a recent problem getting a pivot table to display its data on the BAM portal. I went thru a series of steps to get it working, so in case you have a similar problem here are some things to check to get the data displaying.

To frame things: I had a BAM activity with a series of data points to be tracked. I needed to extract a very simple count/sum cube from the data.

I set up the activity and went thru the view setup in Excel. I had a dimension which tracks a calling system and a then I wanted to get a count/sum of another field which was setup as an integer.

I couldn’t get it working; the view in the portal wouldn’t show any data at all. The calling system wouldn’t appear and neither would the count. I tried to set up the view as both a count (like a rowcount) and a sum and neither one showed the data.

I was pretty sure that I set the activity up correctly so I began to look at different things that could be wrong.

One of the first was to check if the SQL job "TrackedMessages_Copy_BizTalkMsgBoxDb" was running, it was.

Next, I used the SQL Management Studio to connect to Analysis Services on the server that BAM was configured on. I located the cube I was interested in, right clicked on it and select "Process...", data should have appeared. It didn’t. Something else was wrong.
I checked the associated SSIS package which is called BAM_AN_ which is responsible for populating the cube. I ran the package and was finally able to see the data in the BAM Portal.

Documentation for this can be seen here: http://msdn.microsoft.com/en-us/library/aa560072.aspx

So, to get things working right, I scheduled the SSIS package to the correct time interval and things were on track.

Activity Missing from your BAM Portal? This May be Why...

Recently I worked on a BAM implementation and found myself stumped as to why, after creating a new activity, it was not visible in the portal.

I thought it might have been a mistake in my configuration, deployment, or a rights issue. After a series of experiments I couldn't determine anything wrong with either my configuration or deployment so I turned to investigating if it was a rights issue or not.

It turned out to be. The following lines saved as a .bat file did the trick. Just replace the bracketed [ ] items with the names from your environment and it will work.

"C:\Program Files\Microsoft BizTalk Server 2006\Tracking\BM"
add-account -AccountName:[DOMAIN\user] -View:[ViewName]
-Server:[ServerName] -Database:BAMPrimaryImport