Posted on Leave a comment

Matching SQL Agent Jobs to SSRS Subscriptions

Run this query on the instance hosting your SSRS databases to get list of SSRS subscriptions and their corresponding SQL Agent job names.

   SELECT b.[name] "JobName"
        , e.[name] "ReportName"
        , e.[path] "ReportPath"
        , d.[description] "SubscriptionDescription"
        , a.SubscriptionID
        , laststatus
        , eventtype
        , LastRunTime
        , date_created
        , date_modified
     FROM ReportServer.dbo.ReportSchedule a 
     JOIN msdb.dbo.sysjobs b
       ON cast(a.ScheduleID as varchar(1000)) = b.[name]
     JOIN ReportServer.dbo.ReportSchedule c
       ON a.ScheduleID = c.ScheduleID
     JOIN ReportServer.dbo.Subscriptions d
       ON c.SubscriptionID = d.SubscriptionID
     JOIN ReportServer.dbo.[Catalog] e
       ON d.report_oid = e.itemid

This can be useful if you need to explicitly execute the subscription directly from SQL or need to customize the SQL Agent job in some way such as preventing the report from being run based on conditions that cannot be defined in SSRS subscription manager.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.