Reporting Services – Subscription Management Query
CodeSnippets #2
This is a query I would run when I needed to quickly make bulk changes to Reporting Services subscriptions. It’s part of an “emergency fix” toolkit.
Maybe a DB has went down and I have to quickly suspend specific subscriptions or locate Agent jobs for subscriptions. This was always a quick starting point.
I could take the generated Start, Enable and Disable commands and record these in tickets or email threads to demonstrate actions taken. There are other ways to make bulk changes to SSRS subscriptions involving custom queries but this can be run immediately, I don’t have to tailor a WHERE clause first. I also wrote previously on managing subscription failures.
This will work with all versions of SQL Server Reporting Services.
USE ReportServer
SELECT
cat.Name,
cat.Path,
sub.Description,
sch.ScheduleID AS AgentJobID,
sch.LastRunTime,
'EXEC msdb.dbo.sp_start_job N''' + CAST(sch.ScheduleID as nvarchar(36)) + ''';' AS StartJob,
'EXEC msdb.dbo.sp_update_job @job_name = N''' + CAST(sch.ScheduleID as nvarchar(36)) + ''', @enabled = 1 ;' AS EnableJob,
'EXEC msdb.dbo.sp_update_job @job_name = N''' + CAST(sch.ScheduleID as nvarchar(36)) + ''', @enabled = 0 ;' AS DisableJob
FROM
dbo.Schedule sch
INNER JOIN
dbo.ReportSchedule rsch
ON sch.ScheduleID = rsch.ScheduleID
INNER JOIN
dbo.Catalog cat
ON rsch.ReportID = cat.ItemID
INNER JOIN
dbo.Subscriptions sub
ON rsch.SubscriptionID = sub.SubscriptionID
The output from this script gives you all you need to know to quickly handle subscription issues.
Thank you, this helped me save a ton of time