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; }