SQL Reporting Services (SSRS) subscription jobs

Anyone who have worked with reporting services have run into the problem of a report subscription not executing a report correctly and the usual response is “how do I re-run this report”. For the novice, we know that all the subscriptions are actual jobs under SQL Server Agent on SQL server. But going there will reveal a trove of obfuscation. All the subscription jobs are named with GUIDs, not very useful to a DBA.

There’s is away to reveal the true name of these jobs and a bit more. Here’s a script you can use to do just that:

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

The ReportSchedule table links the Report ID to the Subscription ID, so this makes a food starting table.
The Schedule and Subscriptions table contain information on just that – subscriptions and schedules but these are stored in GUIDs also so the last table Catalog is required for the user friendly names.