SQL Script to find all SQL Agent Jobs for SQL Reporting Services Subscriptions

Posted on December 18, 2016 by Michael Roma
select 
    job.name as SQLAgentJob,
    subscr.SubscriptionID,
    report.Name, 
    report.Path,    
    subscr.Description,
    subscr.LastRunTime,
    subscr.LastStatus

from 
    Subscriptions subscr
    join
    ReportSchedule sched
    on subscr.SubscriptionID = sched.SubscriptionID
    join
    Catalog report
    on sched.ReportID = report.ItemID
    join
    msdb.dbo.sysjobs job
    on cast(sched.ScheduleID as nvarchar(50)) = job.name

order by 
    report.name
comments powered by Disqus