February 12, 2015 at 1:49 am
Hello
I have now set up quite a few subscriptions.
The only way I can currently gauge whether they work or not is by ccing myself into whatever report is sent out. Clearly as subscriptions grow, this method is not going to be reliable and I may miss a report if it fails to run.
My question is, is there a simple report I can set up to show my subscriptions' status?
Any pointers welcome. Thanks.
February 12, 2015 at 5:37 am
You can use something like this and modify it to your owns needs:
SELECT own.UserName as Owner
,cat.path as Report_Name
,sub.[Description]
,sub.[LastStatus]
,sub.[LastRunTime] as LastRunAttemptTime
,RptSched.ScheduleId as JobId
,sub.Report_OID
,sub.SubscriptionId
FROM [ReportServer].[dbo].[Subscriptions] sub
JOIN [ReportServer].[dbo].[Catalog] cat
on sub.Report_OID = cat.ItemId
JOIN [ReportServer].[dbo].[Users] own
on sub.OwnerId = own.UserId
JOIN [ReportServer].[dbo].[Users] mod
on sub.ModifiedByID = mod.UserId
JOIN [ReportServer].[dbo].[ReportSchedule] Rptsched
on sub.SubscriptionId = rptsched.SubscriptionId
WHERE
sub.[LastRunTime] > Dateadd(hh, -10, GetDate())
Take a look at WHERE clause and modify to your own needs.....
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply