SQL query: hourly processed messages for BizTalk ports

One of my clients wanted to know how many messages were processed on a certain BizTalk port.
To show some statistics, I created a SQL query that lists a message count of the messages processed per hour for a certain Port. The SQL query will run against the BizTalk tracking database BizTalkDTAdb.

Here’s the SQL query I used:

use BizTalkDTAdb

SELECT top 200
      a.[Event/Direction],
      a.[Event/Port],
      CONVERT( VARCHAR(10), a.[Event/Timestamp], 111) as [date],
DATEPART(HOUR,a.[Event/Timestamp]) as [Hour],
COUNT(*) as [Count]
FROM [dbo].[dtav_MessageFacts] a
inner join dbo.btsv_Tracking_Parts b on a.[MessageInstance/InstanceID] = b.uidMessageID
WHERE
a.[Event/Port] =‘RP_SAM.Messaging_SamData_MPM’
AND a.[Event/Direction] =‘Receive’
AND a.[Event/Timestamp] >DATEADD(day,3,GETDATE())
GROUP BY  a.[Event/Port], CONVERT(VARCHAR(10), a.[Event/Timestamp], 111), DATEPART(HOUR,a.[Event/Timestamp]), a.[Event/Direction]
ORDER BY CONVERT(VARCHAR(10), a.[Event/Timestamp], 111), DATEPART(HOUR,a.[Event/Timestamp])

Using Fiddler for viewing exchanged messages with BizTalk

I was trying to view the SOAP messages and headers that were actually exchanged with a certain webservice.
To do so, I used Fiddler2… available from http://fiddler2.com/.

Fiddler2 will allow you to monitor traffic when using HTTP, SOAP or WCF ports in BizTalk. However, Fiddler will not trace any messages sent to endpoints by BizTalk by default as it does not use WinInet. To overcome this issue, you’ll need to configure a proxy to allow Fiddler to intercept the messages.
Note that these proxy settings will need to be removed when Fiddler is not running. Because otherwise all traffic through this proxy will not be received by anything.

In the send port of BizTalk go to the Configuration settings. Open the tab ‘Proxy’ to configure the necessary settings. Now you should change following settings:

  • Server: 127.0.0.1
  • Port: 8888

ProxySettingsInBTS

That’s is all you need to do. Now open fiddler2 and process a message.
If all is configured correctly, you should see the exchanged messages coming through fiddler.
Fiddler

BizTalk 2010 Tracking issue: No tracking on ports

We’ve found a problem with the tracking on all of our ports in BizTalk. As well as the receive and send ports didn’t track any messages, although all proper tracking options were set according to the BizTalk admin console. However, when we did a binding export it only showed PipelineEvents as TrackingOption, so ServiceStartEnd and MessageSendReceive were missing.

When configuring all tracking options on the used pipelines, all was working correctly. So only the tracking options set on the ports itself seemed to be ignored.

The problem appeared to originate in the table StaticTrackingInfo of the BizTalkMgmtDB, where a value of 0x10000000 was present for some of the biztalk pipelines instead of 0x13000000. So to set these records straight, I used this T-SQL command:

begin transaction

update BizTalkMgmtDb.dbo.StaticTrackingInfo set imgData = 0x13000000 where strServiceName in(
 
‘Microsoft.BizTalk.DefaultPipelines.XMLTransmit’
,
‘Microsoft.BizTalk.DefaultPipelines.XMLReceive’,
 
‘Microsoft.BizTalk.DefaultPipelines.PassThruTransmit’,
 
‘Microsoft.BizTalk.DefaultPipelines.PassThruReceive’)

–commit

The problem was rather quickly located thanks to this blog.

Monitor BizTalk Server SQL Job fails

The monitor BizTalk Server SQL Job at one of our customers kept failing. The BizTalk job Monitor BizTalk Server (BizTalkMgmtDb) calls the stored procedure [dbo].[btsmon_Inconsistent]. While this stored procedure calls several other stored procedures to cleanup the messagebox and other meta-tables within BizTalk. Within these stored procedures SQL-commands are generated dynamically to run against the BizTalk databases/tables.

Each time the SQL jobs ran, it gave the error message:

Incorrect syntax near ‘Send_Host_x64_MessageRefCountLog’. [SQLSTATE 42000] (Error 102).  The step failed.

Because of the multiple host instances in the clients configuration, the generated SQL-command can become quite large. And in our case the problem lied within the called stored procedure [dbo].[btsmon_MessagesWithoutReferences].
To generate the SQL-command dynamically, a variable @tsql is used of the type nvarchar(4000)… Now here is where the problem occured. The generated t-SQL exceeded the 4000 characters that are allowed and as a result only part of the command was stored in the variable. And of course because of the partial command, the t-SQL command couldn’t be executed and raised the error shown above.

To resolve the issue, I just modified the stored procedure by changing the type of the variable @tsql from nvarchar(4000) to nvarchar(MAX). This way the dynamically generated command can be larger than 4000 characters.

StoredProcMod

Founding partner for the BTUG.be

There is a new User Group born in Belgium. It’s a brand new BizTalk User Group, to be more precise, and goes by the name of BTUG.be. One of the initiators is someone of our very own company… Mitch Vanhelden, one of our Business Integration consultants working with Microsoft BizTalk.
Cnext is proud to be one of the founding partners of this Belgian BizTalk User Group. The initiative is a joined effort, together with some of the other leading partners on Business Integration using Microsoft technologies.

BtugLogo-Small

As a kick off event we’re targeting a full day event somewhere in November. For more news on the progression to organize this event and much more on the User Group, you should keep an eye out at the website http://www.btug.be or follow the twitter account @BTUGbe.

You’ll be seeing us on the upcoming events of the BTUG.be…. see you there!

Validation error for schema with Any-element

At the company I am working for, the goal is to have Unit tests for every mapping and flow. One of the schemas we are using is a generic schema that we use to send insert statements to SQL Server. This schema contains an <Any>-element, as you can see in below image.

Schema

We tried validating an XML message as below.

XML Message to validate

As you can see, technically this document should validate just fine since the root node is correct, namespace is correct and the rest of the body content should be ignored. However, when trying to validate the message using Visual Studio or via BizUnit, we get following error:

Error Message on validation

Solution

To resolve this issue, you should go back to the schema in Visual Studio. Now go to the XSD definition of the file to be generated and select the <Any> element. And view the properties of this node. We are especially interested in the ‘Process Content’ property. Initially, this property will have the value (Default) set and as a result the processContents attribute will not be added to the <Any>-element. With these settings BizTalk will validate the content of this item, causing the validation error as mentioned.

Schema Any-element Properties

Now change the value of this ‘Process Content’ property to Skip and you will notice the processContents attribute will be added to the <Any>-element in the XSD schema definition.

Changed Schema with the ProcessContent attribute

Having the property set to Skip, BizTalk will not validate the content of the -element. This is exactly the behavior that we expected and this time our test succeeded.

Succesful validation test

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.