How to Match Your Subscription Names with its Job Id

If you've worked on any reporting solution using SSRS, you may have been able to take advantage of subscription feature.  Subscriptions allow you to schedule a repeating or one time request to receive a specific report at a specific time.  Now, you create the subscription in a very simple, straight-forward interface on the report manager, but what you're actually doing is creating a job on your report server with a schedule associated with it.  Just like a job you would create for you SSIS packages.  The only thing wrong with this is when you create that subscription, the name of the subscriptions is not applied to the name of the job on your report server.  So when you go through and create 1 or 2 subscriptions on each of the reports you have deployed, it can get pretty chaotic.

You might be saying, "well, who cares?  I'll just set my subscriptions and leave 'em alone."  Well the minute you have to test run a subscription (off of its set schedule), you will care.  It's a lot easier to go to your report server and run a job than it is to go reset the subscription for 1 minute in the future and then reset it again to the original scheduled time once it runs.  That is, it's a lot easier IF you know what subscription belongs to what Job ID...  Well here's the meat and potatoes of the post.  You can use the following query to match the Subscription Name with the Job ID and the Name of the report being run within the subscription!

SELECT Subscriptions.Description AS SubscriptionName
   ,Schedule.ScheduleID AS JobID
   ,Catalog.Name AS ReportName
FROM dbo.Subscriptions
 INNER JOIN dbo.ReportSchedule
  ON ReportSchedule.SubscriptionID = Subscriptions.SubscriptionID
 INNER JOIN dbo.Schedule
  ON ReportSchedule.ScheduleID = Schedule.ScheduleID
 INNER JOIN dbo.Catalog
  ON ReportSchedule.ReportID = Catalog.ItemID
  AND Subscriptions.Report_OID = Catalog.ItemID
ORDER BY CAST(Schedule.ScheduleID AS VARCHAR(100))