sql-server - SSRS 订阅表和 ExecutionLog3 视图
问题描述
我们有许多具有多个订阅的报告,我希望能够通过查询订阅表和 ExecutionLog3 视图来监控执行情况。
表和视图之间似乎没有明显的关系。
一份特定报告有一份数据驱动订阅。报告多次执行,每次使用不同的参数值。我特别想监控这个订阅的进度。我希望能够识别它何时失败、何时以及使用了哪个参数。
我在这里遇到了一个可能的解决方案,但他接受的答案是错误的 - 见评论
解决方案
看看这是否适合你......我已经使用了很长一段时间了。
SELECT USR.UserName COLLATE Latin1_General_100_CI_AS_KS_WS AS SubscriptionOwner
,SUB.ModifiedDate as ModifiedDate
,SUB.[Description] COLLATE Latin1_General_100_CI_AS_KS_WS As Description
,SUB.EventType COLLATE Latin1_General_100_CI_AS_KS_WS as EventType
,SUB.DeliveryExtension COLLATE Latin1_General_100_CI_AS_KS_WS As DeliveryExtension
,SUB.LastStatus COLLATE Latin1_General_100_CI_AS_KS_WS as LastStatus
,SUB.LastRunTime As LastRunTime
,SCH.NextRunTime As NextRunTime
,SCH.Name COLLATE Latin1_General_100_CI_AS_KS_WS AS ScheduleName
,CAT.[Path] COLLATE Latin1_General_100_CI_AS_KS_WS AS ReportPath
,CAT.[Description] COLLATE Latin1_General_100_CI_AS_KS_WS AS ReportDescription
,CAT.Name COLLATE Latin1_General_100_CI_AS_KS_WS AS ReportName
FROM ReportServer.dbo.Subscriptions AS SUB
INNER JOIN ReportServer.dbo.Users AS USR
ON SUB.OwnerID = USR.UserID
INNER JOIN ReportServer.dbo.[Catalog] AS CAT
ON SUB.Report_OID = CAT.ItemID
INNER JOIN ReportServer.dbo.ReportSchedule AS RS
ON SUB.Report_OID = RS.ReportID
AND SUB.SubscriptionID = RS.SubscriptionID
INNER JOIN ReportServer.dbo.Schedule AS SCH
ON RS.ScheduleID = SCH.ScheduleID
INNER JOIN ReportServer.dbo.ExecutionLog3 EX
ON Cat.Path = EX.ItemPath AND EX.RequestType = 'Subscription'
ORDER BY 1,10
推荐阅读
- arrays - MongoDB获取日期等于最大日期的结果
- ruby-on-rails - 在 Ruby/Rails 中使用单引号而不是冒号
- java - 如何将 CheckedChange 侦听器传递给自定义视图
- android - 在 android 上共享 URL 不显示为链接。反应原生
- python - 熊猫过滤器,其中当前行字符串包含来自不同列中上一行的字符串
- java - 避免在junit4中调用CommandLineRunner
- arrays - 什么时候数组 C-contiguous 和 F-contiguous 同时?
- jenkins - Scripted Pipeline 语法中并行内部的顺序阶段
- python - Python中多点下的面积积分
- r - 使用 mutate 将变量名称列表传递给自定义函数