BAM error: Failed to list permissions for BAM view – System.Data.SqlTypes.SqlNullValueException: Data is Null.

I just encountered an error using the BAM views. None of the BAM views could be opened and I got following error:

An unspecified error has occurred.

Use the navigation bar on the left to access Business Activity Monitoring views.
If the problem persists, contact your System Administrator.

BamError

Unfortunately, this error doesn’t give much information on what the problem exactly is…
So of course, the first thing I did next was to check the event viewer. This gave me 3 error messages.

Current User: STG\edncnextEXCEPTION:System.Web.Services.Protocols.SoapException: Internal Server Error.

and

(BAMPortal.PortalApplication) Void LogAllErrors(System.Exception[]): System.Web.HttpException: Error executing child request for /BAM/Pages/Search.aspx. —> System.Web.HttpUnhandledException: Exception of type ‘System.Web.HttpUnhandledException’ was thrown. —> System.Web.Services.Protocols.SoapException: System.Web.Services.Protocols.SoapException: Internal Server Error.
   at Microsoft.BizTalk.Bam.WebServices.Management.BamManagementService.GetViewDetailsAsXml(String viewName)
   at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)
   at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
   at Microsoft.BizTalk.Bam.WebServices.ManagementService.BamManagementService.GetViewDetailsAsXml(String viewName)
   at Microsoft.BizTalk.Bam.Portal.DataAccess.BamDefinitionCache.FetchViewDefinition(String viewName)
   at Microsoft.BizTalk.Bam.Portal.DataAccess.BamDefinitionCache.GetBamDefinition(String viewName)
   at Microsoft.BizTalk.Bam.Portal.DataAccess.Activity.BuildColumnsCollection()
   at Microsoft.BizTalk.Bam.Portal.DataAccess.Activity.EnsureColumnsCollection()
   at Microsoft.BizTalk.Bam.Portal.DataAccess.Activity.ColumnsOfType(ColumnTypes type)
   at Microsoft.BizTalk.Bam.Portal.DataAccess.Activity.EnsureInstanceColumns()
   at Microsoft.BizTalk.Bam.Portal.DataAccess.Activity.get_InstanceColumns()
   at BAMPortal.ColumnsChooser_ascx.GetColumns()
   at BAMPortal.ColumnsChooser_ascx.GetAvailableColumns()
   at BAMPortal.ColumnsChooser_ascx.ReconcileColumns()
   at BAMPortal.ColumnsChooser_ascx.OnLoad(EventArgs e)
   at System.Web.UI.Control.LoadRecursive()
   at System.Web.UI.Control.LoadRecursive()
   at System.Web.UI.Control.LoadRecursive()
   at System.Web.UI.Control.LoadRecursive()
   at System.Web.UI.Control.LoadRecursive()
   at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
   — End of inner exception stack trace —
   at System.Web.UI.Page.HandleError(Exception e)
   at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
   at System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
   at System.Web.UI.Page.ProcessRequest()
   at System.Web.UI.Page.ProcessRequestWithNoAssert(HttpContext context)
   at System.Web.UI.Page.ProcessRequest(HttpContext context)
   at ASP.pages_search_aspx.ProcessRequest(HttpContext context)
   at System.Web.HttpServerUtility.ExecuteInternal(IHttpHandler handler, TextWriter writer, Boolean preserveForm, Boolean setPreviousPage, VirtualPath path, VirtualPath filePath, String physPath, Exception error, String queryStringOverride)
   — End of inner exception stack trace —
   at System.Web.HttpServerUtility.ExecuteInternal(IHttpHandler handler, TextWriter writer, Boolean preserveForm, Boolean setPreviousPage, VirtualPath path, VirtualPath filePath, String physPath, Exception error, String queryStringOverride)
   at System.Web.HttpServerUtility.Execute(String path, TextWriter writer, Boolean preserveForm)
   at System.Web.HttpServerUtility.Transfer(String path, Boolean preserveForm)
   at System.Web.HttpServerUtility.Transfer(String path)
   at BAMPortal.navbar_ascx.TreeViewNav_NodeClicked(Object sender, TreeNodeEventArgs eventArgs)
   at Microsoft.BizTalk.Bam.Portal.ClickableTreeView.OnTreeNodeClicked(TreeNode node)
   at Microsoft.BizTalk.Bam.Portal.ClickableTreeView.RaisePostBackEvent(String eventArgument)
   at System.Web.UI.WebControls.TreeView.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
   at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
   at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
   at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
   at System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
   at System.Web.UI.Page.ProcessRequest()
   at System.Web.UI.Page.ProcessRequestWithNoAssert(HttpContext context)
   at System.Web.UI.Page.ProcessRequest(HttpContext context)
   at ASP.pages_error_aspx.ProcessRequest(HttpContext context)
   at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
   at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)

Both of these errors doesn’t help out at all.

A third and final error message I found in the event viewer was:

Current User: Domain\edncnext

EXCEPTION:
Microsoft.BizTalk.Bam.Management.BamManagerException: Failed to list permissions for BAM view. —> System.Data.SqlTypes.SqlNullValueException: Data is Null. This method or property cannot be called on Null values.
at System.Data.SqlClient.SqlBuffer.get_String()
at System.Data.SqlClient.SqlDataReader.GetString(Int32 i)
at Microsoft.BizTalk.Bam.Management.SecurityModule.ListViewPermissions(String viewName, String& dboUsername)
— End of inner exception stack trace —
at Microsoft.BizTalk.Bam.Management.SecurityModule.ListViewPermissions(String viewName, String& dboUsername)
at Microsoft.BizTalk.Bam.WebServices.SecurityHelper.VerifyViewPermissions(String viewName, IPrincipal user, BamManager bamManager, Boolean throwIfNoPermissions)
at Microsoft.BizTalk.Bam.WebServices.SecurityHelper.VerifyViewPermissions(String viewName, IPrincipal user, BamManager bamManager)
at Microsoft.BizTalk.Bam.WebServices.Management.BamManagementService.GetViewDetailsAsXml(String viewName)

This message doesn’t exactly give a lot of information, but it was the only one with some info in what the underlying problem should be. It had something to do with the permissions on the BAM view.I was however certain that my account had the necessary rights on all BAM views.

Cause

After some investigating I found that the problem was that an account was removed from Active Directory, but still had some BAM permissions defined on it.

Solution

First of all you’ll need to determine what user defined in the SQL BAM tables that was deleted in Active Directory.
When you are aware which account has recently been deleted from Active directory this would be easy… however in my case I wasn’t aware that there were any accounts removed from active directory.
In this case you can use a simple query to find the missing account, as explained in this blog post on msdn

USE BAMPrimaryImport
GO
Select Name,SID,SUser_SName(SID) as UserAccount from sysusers
WHERE ISLogin = 1 AND issqluser = 0 AND isntuser = 1

this will list all DB roles and the third column displays the windows users (and groups) with the User name (or group name). This third column should not contain any null values.
Where the value null appears, should be the account(s) that were deleted in Active directory.

You can use a command prompt to check the user account with this command:

net user UserName /domain

this will result in an error as shown below.

Command

So this clearly is the account that was deleted from Active Directory.

Now then… how to remove this account from BAM?

In the msdn blog post I mentioned earlier are 2 methods of manually deleting the user account.
There is a much simpler way to achieve this by using following SQL query

REVOKE VIEW DEFINITION ON USER::[domain\user] TO [BAM_ManagementWS] AS [domain\user]
GO

this should be enough to get your BAM views working again!

A special thanks to the Sandro Pereira blog post on this issue, which helped me to solve the problem quite quickly.

BAM deployment and partitioned tables

In this post I will give a brief overview of some problems that arise during deployment of BAM activity updates. In particular when partitioning is enabled for the activity the error below can occurre upon regeneration of the view that union the partitions.

“All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists”

 

When using BAM often the first deployment contains just a few data items and milestones. But when users become aware of the possibilities of this technology they want to build richer queries. Therefore I frequently need to add data items and milestones in my existing activity definitions. Of course business users don’t want to lose the existing BAM data. So I can almost never redeploy the activity definition which would delete all the data.
Now the bm.exe tool provides me with an option to update an activity. In this case no data would be lost. But after playing around for a while with this ‘update-all’ feature I discovered some problems. 
 
Let me explain what happens if one would like to add a data item to an activity which is already deployed and for which a view is created.
 
In my test environment I called my activity “BAMdeployment” and my view “BAMdeploymentView”. After first deployment the activity contains one data item StartData and two milestones Start and Stop. In the second deployment I want to add a StopData item to the activity and view. First I modified my spreadsheet and then exported the updated definition file.
 
Because I don’t want to lose data, I used the update-all command. Seems straightforward but I got the following error.

  

 
After looking around on the net I found some posts about this error and the solution is to first remove the view before running the update-all command. So after removing the view, the update-all completed without errors. The new data item StopData became visible in my view. Fine! But not for long.
 
The tests I did till now were done on a small set of data. But in a real scenario I probably have already a lot of instances of my activity in the BAMPrimaryImport database. Hence BAM archiving/partitioning becomes an issue.
 
After deploying an activity an SSIS package is created for partitioning and archiving the data. Here you can find the theory about this partitioning. Its all about performance.
 
When I tried to repeat the ‘update-all’ deployment strategy on my activity but now with partitioned tables I ran again into trouble. I got a new error.

 
 
Digging into this I ended up with a view in the BAMPrimaryImport database with the name bam_BAMdeployment_CompletedInstances. This view runs a UNION statement for every partitioned table. My custom view (the one defined in the spreadsheet) is afterwards build upon this one. This guarantee that all the data within my predefined activity window will be shown in the BAM portal.
 
Now the problem is that the partitioned tables are not updated with the new data item StopData. So the regeneration of the view bam_BAMdeployment_CompletedInstances fails. Only the first table bam_BAMdeployment_Completed has the new StopData column. While all the subsequent partitioning tables have not. 
 
To fix this problem all new data items and milestones must be added manually in the partitioning tables. While there will never be more than 254 partitioned tables with every activity, this is definitely an exhausting and error prone job to do. So I wrote a script to help you a little bit. Just fill in the name of the activity and the new data item or milestone you want to add. Be sure that you use the same names as given in the definitionfile of the activity. The script will update every partitioning table. 
 

All at once, the right order of actions to take

1/ Update the activity in the spreadsheet and export the definitionfile.

2/ When partitioned tables exist use the script to add the new items.

3/ Remove the views defined for the activity.

4/ Execute the bm update-all command.


-- Script to update the partition tables after adding a data item or milestone to the activity definition.

DECLARE    @activityName nvarchar(128)
DECLARE    @column nvarchar(100)
DECLARE    @columnType nvarchar(100)
DECLARE    @instancePartition sysname

SET @activityName        = 'BAMdeployment'
SET @column             = 'StopData'
SET @columnType         = 'varchar(50)'
BEGIN

IF NOT EXISTS(SELECT ActivityName FROM bam_Metadata_Activities
                 WHERE ActivityName = @activityName)
BEGIN
RAISERROR (N'ActivityDoesNotExist', 16, 1)
RETURN
END
   
DECLARE partition_cursor CURSOR LOCAL FOR
SELECT InstancesTable
FROM [dbo].[bam_Metadata_Partitions]
WHERE ActivityName = @activityName
ORDER BY CreationTime ASC

OPEN partition_cursor
FETCH NEXT FROM partition_cursor
INTO @instancePartition
WHILE @@fetch_status = 0
BEGIN

EXEC ('ALTER TABLE ' + @instancePartition + ' ADD ' + @column + ' ' + @columnType)
    
FETCH NEXT FROM partition_cursor
INTO @instancePartition
END
END
GO

BAM deployment in BizTalk Server 2006

Personally I found that BAM is one of the most useful features in Microsoft BizTalk Server 2006. It brings down the gap between business and IT. Unfortunally like most BizTalk architects/developers I struggled with deployment on my production environment. If you don’t need to keep the data for historical reasons or analysis then deployment of a BAM flow is easy and straight forward. You can just delete your flow and recreate it again. In case your customer requires you to keep your data then deployment becomes trickier.

For those scenario’s our team created the following check list:

Type Questions Undeploy Remove view Update Deploy Data loss
Activity Add Activity    

1

 

N

Activity Add Business Data field    

1

 

N

Activity Add Business Milestone field    

1

 

N

Activity Modify data type of Business Data field (a)

1

   

2

Y

Activity Delete Business Milestone field

1

   

2

Y

Activity Delete Business Data field

1

   

2

Y

Activity Delete Activity

1

     

Y

View Add View    

1

 

N

View Add Activity  

1

2

 

N

View Add Business Milestone item  

1

2

 

N

View Add Business Data item  

1

2

 

N

View Add Alias    

1

 

N

View Add Measurement  

1

2

 

N

View Add Duration  

1

2

 

N

View Add Group  

1

2

 

N

View Add Dimension  

1

2

 

N

View Modify Alias  

1

2

 

N

View Modify Duration  

1

2

 

N

View Modify Group (b)

1

   

2

Y

View Modify Dimension  

1

2

 

N

View Modify Measurement  

1

2

 

N

View Remove Group  

1

2

 

N

View Remove Duration  

1

2

 

N

View Remove Alias  

1

2

 

N

View Remove Business Milestone item  

1

2

 

N

View Remove Business Data item  

1

2

 

N

View Remove Dimension  

1

2

 

N

View Remove Measurement  

1

2

 

N

View Remove Activity  

1

2

 

N

View Remove View  

1

2

 

N

Pivot Add Pivot Table (c)  

1

2

 

N

Pivot Modify Pivot Table (c)  

1

2

 

N

Pivot Remove Pivot Table

1

   

2

Y

 

  1. Change data type of a Business Data field is not possible. The field needs to be dropped and recreated again.
  2. Change of a group is not possible. The group needs to be deleted and recreated
  3. Update will not give an error but nothing will be changed

     

The headers of the check list reference the different actions that one can do with the bm.exe tool.

Some examples:

Remove Business Data field from activity

In this example there are 2 definition files:

  • SampleBAMDefinitionFile V1.1.1.1.xml: contains the current deployed BAM activity
  • SampleBAMDefinitionFile V1.1.1.2.xml: contains the new BAM activity without the Business Data field

In order to update BAM the following commands should be executed:

bm.exe remove-all -DefinitionFile:”SampleBAMDefinitionFile V1.1.1.1.xml”
bm.exe deploy-all -DefinitionFile:”SampleBAMDefinitionFile V1.1.1.2.xml”

 

Remove Business Milestone item

In this example there are 2 definition files:

  • SampleBAMDefinitionFile V1.1.1.1.xml: contains the current deployed BAM activity
  • SampleBAMDefinitionFile V1.1.1.2.xml: contains the new BAM activity without the Business Data field

     

In order to remove the Business Milestone from the view only the following commands should be executed:

bm.exe remove-view –Name:SampleBAMView
bm.exe update-all -DefinitionFile:”SampleBAMDefinitionFile V1.1.1.2.xml”

In case the Business Milestone needs to be removed from the activity then the above logic will not work. In this case the following commands should be executed:

bm.exe remove-all -DefinitionFile:”SampleBAMDefinitionFile V1.1.1.1.xml”
bm.exe deploy-all -DefinitionFile:”SampleBAMDefinitionFile V1.1.1.2.xml”