Tuesday, November 3, 2009

SQL Server Reporting Services (SSRS) : List of All Reports, Subscriptions, and Their Schedules

Its very common running and managing hundreds of reports as a BI developer. But its very hard to maintain all subscriptions as reporting services doesn't provide a sophisticated user interface. But as alternate we can do work with report server system tables. Even though this script is very easy and known by all I am just writing the Script here just like that. To find list of subscriptions and their schedules.

SELECT
C.Name,
C.[Path] ObjectPath,
U.UserName,
SB.[Description],
S.StartDate,
S.NextRunTime,
S.LastRunTime,
S.EndDate,
S.RecurrenceType,
S.LastRunStatus,
S.MinutesInterval,
S.DaysInterval,
S.WeeksInterval,
S.DaysOfWeek,
S.DaysOfMonth,
S.[Month],
S.MonthlyWeek
FROM ReportServer..Catalog c WITH (NOLOCK)
INNER JOIN ReportServer..ReportSchedule R ON R.ReportID = c.ItemID
INNER JOIN ReportServer..Schedule S WITH (NOLOCK) ON R.ScheduleID = S.ScheduleID
INNER JOIN ReportServer.dbo.Subscriptions SB ON SB.SubscriptionID = R.SubscriptionID
INNER JOIN ReportServer.dbo.Users U ON U.UserID = SB.OwnerID


Thanks,

No comments:

Post a Comment