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
|