Reports and SSRS - Get Report Subscriptions

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

Description

Retrieves the subscriptions for one or many reports.

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

Code

Stored Procedure:


DROP PROC usp_GetReportSubscriptions
GO

CREATE PROC usp_GetReportSubscriptions @ReportName NVARCHAR(850) = NULL
AS

/****************************************************
Purpose:   To retrieve the subscriptions for one or many reports.
           Uses the Catalog, ReportSchedule and Schedules tables
           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
          
*****************************************************/

SELECT
  
c.ItemID                                        AS ReportID,
  
c.Name                                          AS ReportName,
  
c.Path                                          AS ReportFolder,
  
su.Description                                  AS SubscriptionDescription,
    
su.EventType                                   AS SubscriptionEventType,
    
su.LastStatus                                  AS SubscriptionLastStatus,
    
CONVERT(VARCHAR(20), su.LastRunTime, 113)        AS SubscriptionLastRunTime,
    
su.Parameters                                  AS Subscriptiontype,
    
sch.Name                                       AS ScheduleName,
    
sch.EventType                                  AS ScheduleEventType
FROM Subscriptions su
INNER JOIN ReportServer.dbo.Catalog c (NOLOCK)
  
ON su.Report_OID = c.ItemID
INNER JOIN ReportServer.dbo.ReportSchedule rsc  (NOLOCK)
  
ON rsc.ReportID = c.ItemID
  
AND rsc.SubscriptionID = su.SubscriptionID
INNER JOIN ReportServer.dbo.Schedule Sch  (NOLOCK)
  
ON rsc.ScheduleID = sch.ScheduleID
WHERE c.Name LIKE '%' + ISNULL(@ReportName, '') + '%'
ORDER BY
  
c.Name,
  
Su.Description

ċ
usp_GetReportSubscriptions.sql
(1k)
Andy Hughes,
Jul 28, 2012, 9:45 AM
Comments