Get Report Subscriptions

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