How to find which SQL Agent Schedule is running which Report Subscription


SSRS leverages SQL agent to send subscription emails.  When it creates these jobs, it names the with guids like this:

Not exactly very friendly, is it?  I suppose the point of the thing is that you ought to be using the report manager site or web service to manage those subscriptions.  Still, it is handy to know which schedule belongs to which report subscription.  For example, you might need to troubleshoot permissions issues with executing the subscription or you may want to kick one off manually while testing.

You can find the guid schedule name for a report with the following query against the ReportServer database:

       c.Name as ReportName
       , c.Path
       , s.ScheduleId as SQL_Agent_Job_Name
       , s.LastRunTime
       , s.NextRunTime
       , sub.Description as Subscription_Description
       , sub.LastStatus
FROM dbo.Catalog c
       join dbo.ReportSchedule rs on c.itemid = rs.ReportId
       join dbo.schedule s on s.ScheduleId = rs.ScheduleID
       join dbo.Subscriptions sub on sub.SubscriptionId = rs.SubscriptionId
WHERE c.Type = 2 --report

       and c.Name = '<Report Name>'