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.