In my office, many of us use SSRS to schedule recurring reports. I want to view the schedule of upcoming report runs, for the next few days or a week, so that we can see whether 20 reports are all going to try to run at the same time. How can I accomplish that? I've created t-sql reports that show subscription information, but they only show "last run" dates and times. That's not helpful for predicting tomorrow's bottlenecks. The solution should include data-driven subscriptions too.
Sql – is there a way to query future SSRS subscription schedules
reporting-servicessqlsubscriptions
Best Answer
SSRS stores all of its data in the ReportServer database so you'll need an account with read access to this database. This is all relevant for SSRS running in native mode. I'm also not sure if shared schedules or data-driven subscriptions will be handled by this code, but I'm pretty sure they will be. I just haven't tested them.
PLEASE NOTE: Microsoft does not recommend or support directly querying the ReportServer database. They could change the structure in the next version or update of SSRS and you likely won't get any warning. The Microsoft recommendation is to always use the SSRS web service when you need to interrogate information about reporting services.
These are the tables that are relevant for pulling out the subscription information:
The SQL below pulls out schedule interval information for all reports. It doesn't calculate the next run dates but by figuring out the interval that the schedule is supposed to run on you can write another query to generate the actual dates.
This SQL was originally written for a report that just displays a string describing the interval so the final output is probably not what you're after. It should give you a good starting point though since it does figure out all of the interval details.