Reports and SSRS - 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

*/


ċ
usp_GetReportSchedules.sql
(1k)
Andy Hughes,
Sep 29, 2014, 11:07 AM
Comments