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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
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.
