SQL Server Reporting Service – Find out a report schedule job name

Leave a comment (0) Go to comments

Currently I was working with one of my developer and he asked me to run a report once again so that subscribers to that reports will get the updated report.

I said, if I execute a report, I mean to say, if I open a report Server url and click on required report, I will be able to see the result but how the subscribers will get a report ? Even developer was unsure about this.

This became a problem statement. Just for information, Our company has 100+ SSRS reports with daily, weekly, monthly and adhoc schedules with various rendering formats like excel, pdf, csv, xml and tiff sent to multiple users, file share (departments).  This was all scheduled, bur this time we have to manually run a subscription to resend reports on an adhoc basis as actual subscriptions failed.

You can not find a SQL Server Agent job easily as job names are nothing a 32 byte long unique identifier value, by looking at this, you can not track which job is associated to which report subscription.

Those job names will be displayed like this

SQL Server Reporting Service   Find out a report schedule job name SSRS Report name and associated SQL Server agent job name SQL Script to find out SQL Server Agent JOB associated with Report and subscription job is associated to which Report and which subscription


Long story in short, I need to find out a SQL Server AGENT JOB name which is responsible for executing a report subscription.

The following Script will help you to figure out

  1. Which job is associated to which Report and which subscription
  2. What is subscription Delivery extension
  3. Report name and associated SQL Server agent job name
  4. Report Path, where the output report will be saved  / delivered

SQL Script to find out SQL Server Agent JOB associated with Report and subscription

use REPORTSERVER
go
SELECT     Schedule.ScheduleID AS SQLServer_Agent_Job_Name, 'Save in \\FILESHARE01\REPORTS\PROD\2011\' AS 'Subscriptions Description', Subscriptions.DeliveryExtension AS 'Subscriptions.DeliveryExtension', 
                      [Catalog].Name AS ReportName, [Catalog].Path AS ReportPath
FROM         ReportSchedule INNER JOIN
                      Schedule ON ReportSchedule.ScheduleID = Schedule.ScheduleID INNER JOIN
                      Subscriptions ON ReportSchedule.SubscriptionID = Subscriptions.SubscriptionID INNER JOIN
                      [Catalog] ON ReportSchedule.ReportID = [Catalog].ItemID AND Subscriptions.Report_OID = [Catalog].ItemID
where [Catalog].Name = '<myreportname>'

OUTPUT of Above Script

SQL Server Reporting Service   Find out a report schedule job name SSRS Report name and associated SQL Server agent job name SQL Script to find out SQL Server Agent JOB associated with Report and subscription job is associated to which Report and which subscription

EOF - SQL Server Reporting Service – Find out a report schedule job name, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.