December 21, 2010 at 2:51 pm
Hi,
How are you? We have 500 reports and subscriptions. We are not able to see every day every subscription status wheather it is succesfully ran or not.
Is there any way to send an alert of all failure subscriptions by email or any other way?
Thanks,
BMR
December 22, 2010 at 7:01 am
You can query the reporting services database for information about subscriptions and last run status. The query below, run it against your reporting services database, is something I use to review a number of report and subscription items. It doesn't answer your question directly, but I think it may lead you in the right direction.
SELECT cat.[Name]
,cat.[Path]
,res.ScheduleID AS JobID
,sub.LastRuntime
,sub.LastStatus
,sch.next_run_date
,sch.next_run_time
,LEFT(CAST(sch.next_run_date AS CHAR(8)),4)
+ '-' + SUBSTRING(CAST(sch.next_run_date AS CHAR(8)),5,2)
+ '-' + RIGHT(CAST(sch.next_run_date AS CHAR(8)),2)
+ ' ' + CASE WHEN LEN(CAST(sch.next_run_time AS VARCHAR(6))) = 5
THEN '0' + LEFT(CAST(sch.next_run_time AS VARCHAR(6)),1)
ELSE LEFT(CAST(sch.next_run_time AS VARCHAR(6)),2) END
+':' + CASE WHEN LEN(CAST(sch.next_run_time AS VARCHAR(6))) = 5
THEN SUBSTRING(CAST(sch.next_run_time AS VARCHAR(6)),2,2)
ELSE SUBSTRING(CAST(sch.next_run_time AS VARCHAR(6)),3,2) END
+ ':00.000' AS NextRunTime
,CASE WHEN job.[enabled] = 1 THEN 'Enabled' ELSE 'Disabled' END AS JobStatus
FROM dbo.Catalog AS cat
INNER JOIN dbo.Subscriptions AS sub
ON cat.ItemID = sub.Report_OID
INNER JOIN dbo.ReportSchedule AS res
ON cat.ItemID = res.ReportID
AND sub.SubscriptionID = res.SubscriptionID
INNER JOIN msdb.dbo.sysjobs AS job
ON CAST(res.ScheduleID AS VARCHAR(36)) = job.[name]
INNER JOIN msdb.dbo.sysjobschedules AS sch
ON job.job_id = sch.job_id
ORDER BY cat.[Name]
December 22, 2010 at 3:04 pm
Thank you,
BMR
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply