Tuesday, January 14, 2014

SSRS Subscriptions and their SQL Server Agent job names

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:

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