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.