Biztalk数据库查询相关问题 [英] Biztalk database querying related question

问题描述

I有一个exe应用程序，必须找出我的应用程序的任何实例是否持有超过一些x minuites。

I have an exe application which has to find out if any instance of my application is holding there for more than some x minuites.

为此我调查表BizTalkMsgBoxDb.BizTalkServerApplicationQ。我面临的问题是，我不想要由于其他应用程序而失败的实例，而是我只需要处于上述条件下的实例。

For this i qurying the table BizTalkMsgBoxDb.BizTalkServerApplicationQ. The problem i am facing is, i do not want to the instances which are failing due to other applications, instead i only need the instances which are falling under the above mentioned condition.

在BizTalkMsgBoxDb.BizTalkServerApplicationQ表中有几个coulumns可用（比如uidServiceID，uidClassID），​​我认为我可以通过它来创建条件。但我无法理解准备联接查询的表关系。

There are couple of coulumns available(like uidServiceID,uidClassID) in the BizTalkMsgBoxDb.BizTalkServerApplicationQ table by which i think i can make the condition. but i am unable to understand the table relations to prepare the join query.

任何人都可以帮我准备正确查询我的要求。

Can any one help me in preparing the correct query for my requirements.

提前致谢!!。

推荐答案

我会看一下样本在HAT中查询要使用的SQL查询的示例。查询菜单为您提供了一些您可以稍微自定义的预构建查询。

I would look at the sample queries in HAT for examples of a SQL query to use. The queries menu gives you some pre-built queries that you can customize slightly.

您可以自定义名为"运行时间超过24小时的服务"的示例查询。如下所示：

You could customize the sample query called "services running longer than 24 hours" as seen below:

SELECT
[服务/名称]，[服务/类型]，
[ServiceInstance / State]，
dateadd（分钟，@ UtcOffsetMin，[ServiceInstance / StartTime]）为[StartTime]， - 不能使用'as [ServiceInstance / StartTime]'，因为这会阻止SQL使用索引该列（与ORDER BY冲突）
dateadd（分钟，@ UtcOffsetMin，[ServiceInstance / EndTime]）为[EndTime]， - 不能使用'as [ServiceInstance / EndTime]'，因为这会阻止SQL使用索引在该列上（与ORDER BY冲突）
[ServiceInstance / Duration]，
[ServiceInstance / ExitCode]，
[ServiceInstance / ErrorInfo]，
[ServiceInstance / Host]，
[服务/ AssemblyName]，
[ServiceInstance / InstanceID]，
[ServiceInstance / ActivityID]，
[Service / ServiceGUID]，
[Service / ServiceClassGUID]
FROM [dbo]。[dtav_ServiceFacts] AS sf WITH（READPAST）
JOIN [dbo]。[dta_ServiceState] WITH（READPAST）ON strState = [ServiceInstance / State] < br> - Started = 1 -
WHERE nServiceStateId = 1

SELECT 
 [Service/Name], [Service/Type],
 [ServiceInstance/State],
 dateadd(minute, @UtcOffsetMin, [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, @UtcOffsetMin, [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] AS sf WITH (READPAST)
JOIN [dbo].[dta_ServiceState] WITH (READPAST) ON strState = [ServiceInstance/State]
-- Started=1 --
WHERE nServiceStateId= 1

- 为X分配一个分钟数的值：

-- Subsitute a value for X for the number of minutes:

- 60000是一分钟内的毫秒数。
和[ServiceInstance / Duration]> 60000 * X
订购[ServiceInstance / StartTime]

-- 60000 is the number of milliseconds in one minute.
AND [ServiceInstance/Duration] > 60000* X
ORDER BY [ServiceInstance/StartTime]

如果您的服务实例遇到错误，那么您只需要自定义查询"最近100个有错误的已终止实例"。

If your service instances are encountering an error then you would just need to customize the query "most recent 100 terminated instances with errors".

谢谢，

这篇关于Biztalk数据库查询相关问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！