Roll your own "HAT" queries.

Here's some more research from using SQL Profiler. The question is what does "HAT" do when you click "Run Query" in the "Operations/Service Instances" screen.

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

This stored proc starts with a query, then keeps adding to it. I.e. it builds it's sql query as a variable, then executes it dynamically.

So inside the stored proc, you will see many calls to MBOM_AppendClauseToQueryString, then at the very end you will see
exec (@tsql).

I think I trapped the SQL command once, and it looked something like this:

use biztalkdtadb
SELECT top 100
[Service/Name], [Service/Type],
[ServiceInstance/State],
dateadd(minute, 60, [ServiceInstance/StartTime]) as [StartTime], -- can''t use ''as [ServiceInstance/StartTime]'' since this prevents SQL from using index on that column (conflicts with ORDER BY)
dateadd(minute, 60, [ServiceInstance/EndTime]) as [EndTime], -- can''t use ''as [ServiceInstance/EndTime]'' since this prevents SQL from using index on that column (conflicts with ORDER BY)
[ServiceInstance/Duration],
[ServiceInstance/ExitCode],
[ServiceInstance/ErrorInfo],
[ServiceInstance/Host],
[Service/AssemblyName],
[ServiceInstance/InstanceID],
[ServiceInstance/ActivityID],
[Service/ServiceGUID],
[Service/ServiceClassGUID]
FROM dbo.dtav_ServiceFacts sf WITH (READPAST)
--where [ServiceInstance/InstanceID] = 'DB6AF815-C28F-48C2-8D68-E0CC45B79157'
ORDER BY sf.[ServiceInstance/StartTime] desc