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
*/
|
 Updating... Andy Hughes, Sep 29, 2014, 11:07 AM
|
