Get Report Schedules

Applicability:

SQL Server 2000: Not Tested

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Not Tested

Credits:

Author: Sorna Kumar Muthuraj

Date: 9 Jul 2011

29 Sep 2014 - ChillyDBA - extended to return Report Name

Description

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'

Code

Stored Procedure:

DROP PROC usp_GetReportSchedules

GO

CREATE PROC usp_GetReportSchedules

AS

/****************************************************

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

*****************************************************/

SELECT

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

*/