For those that run SQL Server Reporting Services and have a high volume of report subscriptions, you probably know the pain of trying to figure out which jobs in SQL Server Agent kick off which subscriptions in Reporting Services.  This is a query that you can put into a report of its own so you can pull it up and reference in the event you want to kick off a job (and subscription) manually: (Make sure you select the ReportServerDB before executing the query below)

SELECT Schedule.ScheduleID AS JobName
,[Catalog].Name AS ReportName
,Subscriptions.Description AS Recipients
,[Catalog].Path AS ReportPath
FROM dbo.ReportSchedule
INNER JOIN dbo.Schedule ON ReportSchedule.ScheduleID = Schedule.ScheduleID
INNER JOIN dbo.Subscriptions ON ReportSchedule.SubscriptionID = Subscriptions.SubscriptionID
INNER JOIN dbo.[Catalog] ON ReportSchedule.ReportID = [Catalog].ItemID
AND Subscriptions.Report_OID = [Catalog].ItemID

Leave a Reply