Finding SSRS subscription details (subscription job name, report name, path, last run status etc) from report database



Although each report’s subscription is configured as a SQL Agent job in the server hosting SSRS’s ReportServer database, it is little tricky to find the corresponding report subscription’s details from job name.
Please use below codes (on the server where SSRS’s ReportServer database is hosted) to find details about report subscription including retrieving report names for SSRS subscription jobs, their last execution status, report’s path etc.

SELECT
      c.Name AS ReportName,
      c.Path AS Report_Path,
      rs.ScheduleID AS JOB_NAME
      , s.[Description]
      , s.LastStatus
      , s.LastRunTime
      ,c.CreationDate
      ,c.ModifiedDate
FROM
      ReportServer..[Catalog] c
      JOIN ReportServer..Subscriptions s ON c.ItemID = s.Report_OID
      JOIN ReportServer..ReportSchedule rs ON c.ItemID = rs.ReportID
      AND rs.SubscriptionID = s.SubscriptionID

Please Note: 
1. This code is tested on SQL/SSRS 2008 2. Do not alter SQL jobs (Subscription Jobs) directly. Always use report’s subscription configaration option to do any change to these jobs. 3. You can run this job from SQL Server agent if you wish to run a SSRS report’s subscripion manually. 

Comments