SSRS Subscription Schedule Started/Completed Events

Question

I am pretty new to SSRS and I am trying to find a way to know when scheduled report is actually started on the server, when it has completed with success of failure and if it was canceled. As of now, I am using the ReportingService2010 class API to talk to the Report Server and the only way that it seems possible to me is to make something custom that checks the schedules and fire events at these times for the started events and to scan the folder where I'm going to save the report and when a new file is added, I know that the report has been successfully created, and maybe add a Timeout event after x time.

I don't think this is a really clean approach and I'm sure that you guys might have an easier answer because I'm sure that there must be a way to do it without manually scanning everything.

I used the ListJobs() method to access all the jobs that are currently running on the server but it doesn't seem to consider when a subscription is done, because, I only get results in the ListJobs() method when I manually click on "Run Now" for a specific report on the server.

Do you guys have any idea?

Thanks a lot,

Claude

Answer 1

There are few tables in 'ReportServer' database to provide you most of your information. e.g Subscriptions table has column as LastStatus, it gives how many subscriptions were processed and status of reports last run. e.g 'Done: 2 processed of 2 total; 0 errors' , 'Pending' ,

sample query would be like below, this is for getting a schedule but you can check and modify as you need.

Setup a new report with this query and schedule it as per your need to give you the status.

SELECT CAT.Name
  ,CAT.[Path] AS ReportPath 
  ,SUB.LastRunTime 
  ,SCH.NextRunTime
  ,CONVERT(VARCHAR(10), CONVERT(datetime, SCH.NextRunTime,   1), 101) As RunDate
  ,right(convert(varchar(32),SCH.NextRunTime,100),8) As RunTime
  ,SUB.[Description] 
  ,SUB.EventType 
  ,SUB.LastStatus 
  ,SUB.ModifiedDate 
  ,SCH.Name AS ScheduleName     
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 
--Where CONVERT(VARCHAR(10), CONVERT(datetime, SCH.NextRunTime,   1), 101)  
= CONVERT(VARCHAR(10), CONVERT(datetime, getDate()+1,   1), 101) 
ORDER BY USR.UserName 
    ,CAT.[Path];