Reports and SSRS - Get Report Schedules


                 SQL Server 2000:        Not Tested        
                 SQL Server 2005:        Tested
                 SQL Server 2008:        Tested
                 SQL Server 2008R2:    Tested
                 SQL Server 2012:        Not Tested        


Author:    Sorna Kumar Muthuraj
Date:        9 Jul 2011
                29 Sep 2014 - ChillyDBA - extended to return Report Name


Retrieve a list of all schedules currently defined on the Report Server.

NOTE:  It is assumed that the Report Server Database has its default name of 'ReportServer'


Stored Procedure:

DROP PROC usp_GetReportSchedules

CREATE PROC usp_GetReportSchedules

Purpose:   To retrieve a list of all schedules currently defined on
           the Report Server.
           Uses the Schedules table in the report server
           database (assumed to be called by its default
           name of 'ReportServer')

Author:        Sorna Kumar Muthuraj
History:   9 Jul 2011 - Initial Issue
           29 Sep 2014 - ChillyDBA - extended to return the report name

c.Name                                  AS ReportName,
s.Name                                   AS ScheduleName,
CONVERT(VARCHAR(20), StartDate, 113) AS StartDate,
CONVERT(VARCHAR(20), EndDate, 113)       AS EndDate,
CONVERT(VARCHAR(20), NextRunTime, 113)   AS NextRunTime,
CONVERT(VARCHAR(20), LastRunTime, 113)   AS LastRunTime,
LastRunStatus                            AS LastRunStatus,
CASE RecurrenceType
WHEN  1 THEN 'Once'
WHEN  2 THEN 'Hourly '
WHEN  4 THEN 'Daily / Weekly'
WHEN  6 THEN 'Monthly'
END                                      AS RecurrenceType,
EventType                                 AS EventType
FROM dbo.Schedule (NOLOCK) s
INNER JOIN dbo.ReportSchedule rs
ON s.ScheduleID = rs.ScheduleID
INNER JOIN dbo.Catalog c
ON rs.ReportID = c.ItemID
ORDER BY c.Name, s.Name

   EXEC usp_GetReportSchedules


Andy Hughes,
Sep 29, 2014, 11:07 AM