:::: MENU ::::

Get scheduled job name for specific SSRS report subscription

  • Sep 24 / 2013
  • 0
dbDigger, Reporting Services SSRS, T-SQL Scripts

Get scheduled job name for specific SSRS report subscription

I was required to rerun some failed SSRS subscriptions. As we know that SSRS subscriptions automatically create scheduled jobs in SQL Server agent. The problem is that job names are not readable and it is some UID. So first of all i got the list of failed subscriptions from execution logs and then i was required to get job name for each failed subscription so that it may be executed manually. For this purpose i found following piece of code and it worked fine for me. In following example i got the job name for a report named MyReport.

SELECT b.NAME AS JobName,e.NAME  ,e.path  ,d.description  ,a.SubscriptionID  
   ,laststatus  ,eventtype  ,LastRunTime  ,date_created  ,date_modified  
 FROM ReportServer.dbo.ReportSchedule a  
 JOIN msdb.dbo.sysjobs b ON a.ScheduleID = b.NAME  
 JOIN ReportServer.dbo.ReportSchedule c ON b.NAME = c.ScheduleID  
 JOIN ReportServer.dbo.Subscriptions d ON c.SubscriptionID = d.SubscriptionID  
 JOIN ReportServer.dbo.CATALOG e ON d.report_oid = e.itemid  
 WHERE e.NAME = 'MyReport'  
 and b.description = 'This job is owned by a report server process. Modifying this job could result in database incompatibilities. Use Report Manager or Management Studio to update this job.'   

Above piece of code may be found in full context here.

Consult us to explore the Databases. Contact us