I was looking to accomplish via software the process of going to HAT, listing all Serivce Instances, and then selectively terminating service instances that have a specific error message.

We have some “errors” that aren’t really errors. In other words, our orchestrations get suspended when we call a web service to a legacy system, and for example the shipping address if over 30 characters. We didn’t try this error, and right now I’m not going to suggest we change and remigrate the orchestrations. So I simply want to run a daily script that finds all suspended orchestrations that have “shipping addres over 30” in the error message, and terminate them. That way, when a person looks in HAT, only the more interesting errors are seen.

The WMI to list the suspended service instance took about six minutes to run, while HAT took about six seconds or less. So I finally decided that HAT had to be using SQL directly, not using WMI, and I sought out on a trek to find how HAT did what it did. I used SQL profiler to start a trace and capture all SQL command to the Biztalk databases, then went to HAT, ran the desired processes, when went back to profiler to see what the real SQL commands actually were.

To list the suspend service instances, this is the call to a stored proc:

use BiztalkMsgBoxDb
exec MBOM_LookupInstances @nvcHost = NULL, @nServiceClass = 1, @uidServiceType = NULL, @uidInstanceID = NULL, @nvcUserState = NULL, @nStatus = 63, @nPendingOperation = 15, @dtFrom = NULL, @dtUntil = NULL, @nMaxMatches = 200

To delete one of the suspended service instances, call this Stored Proc and pass the three GUIDs:

exec [dbo].[bts_AdminCompleteTerminate_BizTalkServerApplication]
@uidInstanceID = N'{0E6DF4B2-76D4-46D8-AF80-9B0C07907E38}’,
@uidServiceID = N'{7825F761-716A-5253-7592-A62A4D711BD1}’,
@uidClassID = N'{226FC6B9-0416-47A4-A8E8-4721F1DB1A1B}’,
@nResult = 1

So now, I am using a VBScript with ADODB to run the stored procs, and I’m happy with the results.