How to get a list of SSRS schedules for subscriptions
-
Is there a way out there to interrogate the reporting services db to list all reports, subscriptions and their schedules? I'd rather not go through all the reports individually to identify this information, and I'd like to have a master record I can quickly refer to when I need to find, for example, all the reports that are scheduled to be pushed out at 6am every day.
Is there anyone out there who has a script/query that might handle this?
Help!
Thank you!!
-
use Subscriptions table. This table will list all the scheduled subscriptions details.
select * from subscriptions
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Please feel free to let me know if you are not clear or I’ve misunderstood anything.Thanks,
Arunkumar S P
-
Use ReportSchedule table also
select * from ReportSchedule
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Please feel free to let me know if you are not clear or I’ve misunderstood anything.Thanks,
Arunkumar S P -
selectc.Name,
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
fromdbo.catalog c with (nolock)
inner joindbo.ReportSchedule rs
onrs.ReportID = c.ItemID
inner joindbo.Schedule s with (nolock)
onrs.ScheduleID = s.ScheduleID
order byc.name
"Software changes. This is a rather obvious statement, but it is a fact that must be ever present in the minds of developers and architects. Although we tend to think of software development as chiefly an engineering exercise, the analogy breaks down very quickly. When was the last time someone asked the designers of the Empire State building to add ten new floors at the bottom, put a pool on the top, and have all of this done before Monday morning? " : Doug Purdy, Microsoft Corporation
-
Thank you for the info! I'll give it a try.
-
Here's a quick 'n' dirty I use:
SELECTS.ScheduleID,
C.[Path] ObjectPath,
U.UserName,
SB.[Description],
S.StartDate,
S.LastRunTime
FROM ReportServer.dbo.ReportSchedule RS
INNER JOIN ReportServer.dbo.Schedule S ON S.ScheduleID = RS.ScheduleID
INNER JOIN ReportServer.dbo.[Catalog] C ON C.ItemID = RS.ReportID
INNER JOIN ReportServer.dbo.Subscriptions SB ON SB.SubscriptionID = RS.SubscriptionID
INNER JOIN ReportServer.dbo.Users U ON U.UserID = SB.OwnerID
-
These are great queries, thanks everyone. It just seems I've opened up a whole new can of worms with this. Apparently my subscriptions are scattered throughout several servers, because when I run this query on our main RS2005 db, it only shows 3 subscriptions, when I can look on the Report Manager and clearly see 15-20. Aaak!
So now I have to search through all my servers to find any instances with a ReportServer db....
(what a pain, just to get a full list of subscriptions!)
-
Connect to each of the ReportServer DBs and run the query.
Or talk to your sys admin to script this.
"Software changes. This is a rather obvious statement, but it is a fact that must be ever present in the minds of developers and architects. Although we tend to think of software development as chiefly an engineering exercise, the analogy breaks down very quickly. When was the last time someone asked the designers of the Empire State building to add ten new floors at the bottom, put a pool on the top, and have all of this done before Monday morning? " : Doug Purdy, Microsoft Corporation
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply