Strange BizTalk WMI behavior (curious BizTalk SQL)

This week a co-worker raised an issue with a WMI query where he did a simple query for an orchestrations messages, and for some reason not all of them were returned. This behavior exists on both 2006 R2 and 2009, and thus most likely on 2006 as well. The query was simple:

select    *

from    MSBTS_MessageInstance

where    ServiceInstanceID = ‘{9DD50CE0-CC9C-478C-B19D-A3AAFD33ACA3}’

This was supposed to return two messages, but only one was returned, illustrated below in WMIExplorer:

He came up with a solution where he could instead do a LIKE query and get both message instances returned:

select    *

from    MSBTS_MessageInstance

where    ServiceInstanceID LIKE ‘{9DD50CE0-CC9C-478C-B19D-A3AAFD33ACA3}’

Now I was ok with this as a solution, but I wanted to find out a bit about why this happened.

Using SQL Profiler to see what this WMI query meant I found a call that looked like this when I used ‘=’:

exec BOM_LookupMessageReferences

  @nvcHost=NULL,@nServiceClass=127,@uidServiceType=NULL,¨

  @uidInstanceId=‘9DD50CE0-CC9C-478C-B19D–3AAFD33ACA3’,

  @uidMessageId=NULL,@snStatus=63,@nReferenceType=15,

  @dtFrom=‘Oct 25 3009 12:06:31:360PM’,@dtUntil=‘Oct 25 1809 12:06:31:360PM’,

  @nMaxMatches=200

and like this when using ‘LIKE’:

exec MBOM_LookupMessageReferences

  @nvcHost=NULL,@nServiceClass=127,@uidServiceType=NULL,

  @uidInstanceId=NULL,

  @uidMessageId=NULL,@snStatus=63,@nReferenceType=15,

  @dtFrom=‘Oct 25 3009 12:06:42:867PM’,@dtUntil=‘Oct 25 1809 12:06:42:867PM’,

  @nMaxMatches=200

Spot the difference?

Sure enough in one case we send in the (service)instanceId and in the latter, we don’t. This in effect, causes the latter query to return all messages matching the other criteria, and filtering on the serviceInstanceId is made elsewhere, presumably in the code executed by the WMI call, although I haven’t investigated that further.

So what makes the first query return only one message? It’s got the service instance Id with it, and nothing else, so what’s causing it to filter out the single message.

Looking further into the call chain in SQL, the method MBOM_LookupMessageReferences uses methods named MBOM_LookupMessageReferences_<host>, for example MBOM_LookupMessageReferences_BizTalkServerApplication.

In this (these) procedures you can find the following code:

if (@uidInstanceId IS NOT NULL)

    set ROWCOUNT 1

else if (@nMaxMatches > 0)

    set ROWCOUNT @nMaxMatches

So if we send in a serviceInstanceId we will just get a single message instance returned. I’m not sure what the point of this is really, but it seems to be interfering with what we want.

It’s an universal truth that you do best to stay out of the BizTalk databases and their queries. I’m not going to suggest something that I will call a solution in this post, especially not since I haven’t done sufficient testing to see that this doesn’t interfere with something else.

However, from the test I have done, it seems as if the following code change might be what was intended:

if (@uidMessageId IS NOT NULL)

    set ROWCOUNT 1

else if (@nMaxMatches > 0)

    set ROWCOUNT @nMaxMatches

Which gives the result I want for a serviceInstanceId query:

as well as for a messageInstanceID query;

We are not likely to use this alteration, since I’m ok with the way that the LIKE query works, even though it takes a wider scope then necessary. But perhaps this might help or enlighten someone that finds themselves with a similar puzzle.

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }