Learn more about SQL Server tools

mssqltips logo

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories


How to easily identify a scheduled SQL Server Reporting Services report

By:   |   Read Comments (22)   |   Related Tips: > Reporting Services Monitoring

Our company has hundreds of SQL Server Reporting Services reports with daily, weekly, monthly, quarterly, yearly and adhoc schedules with various rendering formats like web archive, excel, pdf, csv, xml and tiff sent to multiple users, departments and groups. Sometimes we have to manually run a subscription to resend reports on an adhoc basis or re-send the reports when the actual subscriptions failed or when the data had to be corrected.

In this tip I will show you an easy way to identify the scheduled Reporting Services report, so you can run that scheduled job to reproduce the reports and delivery of the reports.

SQL Server Reporting services has come a long way from the first time it was released for SQL Server 2000 SP4. Unfortunately there are still some rough corners where it could be improved and one such place is subscriptions.

Manually initiating a subscription is a tedious task in SQL Server Reporting Services. Each subscription in Reporting Services is setup as a SQL Server Agent job, but the job names are NOT intuitive, rather named as a Unique Identifier as shown below.

Here is a screenshot of some of the subscriptions on a Reporting Services instance.

Fortunately, SQL Server Reporting Services comes with a rich set of meta-data that is stored in tables and for today's tip we will take advantage of these tables. There are two different methods to accomplish this task and both of them are outlined below.

Option 1:
This method looks at the dbo.ReportSchedule, dbo.Subscriptions, dbo.Catalog tables of the ReportServer database along with the dbo.sysjobs table of the msdb to figure out the SQL Agent job name. Additional information is also pulled to isolate the correct report when a similarly named report is in multiple folders/paths.


b.name AS JobName
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 'Sales_Report'

From the above resultset, grab the uniqueidentifier for the JobName column and filter the job in SSMS as shown below. Once the correct job is identified, run the SQL Server Agent job to deliver the subscription.

Option 2:
This option is better than the first option and completely eliminates the manual step in Option 1 of having to find and run the SQL Agent job.

In this option, we will generate the T-SQL that is used inside the SQL Agent job directly from the tables and then just run the SQL. It's a pure SQL based solution.

After you run this query, get the column that has the EXEC command for the report you want to re-run and paste the EXEC command into a query window to execute the code to re-run the report.


'EXEC ReportServer.dbo.AddEvent @EventType=''TimedSubscription'', @EventData=''' CAST(a.SubscriptionID AS VARCHAR(40)) + '''' AS ReportCommand
b.name AS JobName
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 'Sales_Report'

Next Steps:

Last Update:

About the author
MSSQLTips author Sankar Reddy Sankar Reddy is a Database Engineer/DBA and SQL Server MVP. He has been working with SQL Server since 2003 in a variety of roles.

View all my tips

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 

SQL tips:

*Enter Code refresh code     

Friday, May 29, 2015 - 3:44:46 PM - Chuck Back To Top

Nice query. Here is a modified version that casts the unique identifiers to sysname to prevent errors when I tried to run it myself.


SELECT 'EXEC ReportServer.dbo.AddEvent @EventType=''TimedSubscription'', @EventData=''' + CAST(a.SubscriptionID AS VARCHAR(40)) + '''' AS ReportCommand
,b.NAME AS JobName
FROM ReportServer.dbo.ReportSchedule AS a
INNER JOIN msdb.dbo.sysjobs AS b ON CAST(a.ScheduleID AS SYSNAME) = b.NAME
INNER JOIN ReportServer.dbo.ReportSchedule AS c ON b.NAME = CAST(c.ScheduleID AS SYSNAME)
INNER JOIN ReportServer.dbo.Subscriptions AS d ON c.SubscriptionID = d.SubscriptionID
INNER JOIN ReportServer.dbo.CATALOG AS e ON d.Report_OID = e.ItemID
WHERE e.NAME LIKE 'Sales_Report'

Thursday, October 02, 2014 - 1:31:04 PM - Lisa Back To Top

Is there a way to get to get the following information

how many schedules run on a given day

how many instances those schedules created (how many emails, ftps, fileshares etc...)

what time the first started

what time the last completed

what time each started, stopped,

time it took to run each schedule

Friday, September 12, 2014 - 1:21:05 PM - Sirisha Back To Top




We have a report already created and subscribed long back. Just from one week, the report is executing twice and getting mail twice. I can see two different start times and end times for that report for the ssrs log.

How to find the root cause and how to fix this issue. I deleted the old subscription and created the new one but still same problem


I need some help




Wednesday, June 18, 2014 - 2:40:04 PM - Gord Cross Back To Top

Thanks very much, great tip! 

Friday, July 05, 2013 - 5:15:44 AM - Carnalito Back To Top

Hi there


I have reporting services with the following scheduled, (Ad hoc - Daily - Weekly - Monthly), all the reports pull the data from the data warehouse. The data warehouse is populated over the night.


The issue i have is - sometimes the warehouse does not populate during the night and all the (Ad hoc - Daily) reports come out blank. To resolve this i have to manualy repopulate the warehouse so the data can be available.


Question is, how do i execute the Ad hoc & Daily reports to render the new data, as their subcription is set to trigger 4 in the morning?

I know there is a way of executing the scheduled job, but finding the the job related to daily reports in a hovek as the names dont make any sense.




Wednesday, June 05, 2013 - 5:34:10 AM - Nevarda Back To Top

HI, I'm not sure if any of you covered this but please could you assist?

I have, for example, a report that has many parameters that change the reports result. Because i need to have more than five(5) results from the same report, i have made 5 subscriptions on it choosing different parameters for each run. My issue is every now and then, one of the five will fail due to some error in calculation because data changes etc. I want to know if there is a way to re-run only that specific subscription for that report of the five that are setup and not the one subscription id that will run all 5 again.

does that make sense? 

I cant just run the entire subscription again because it emails many institutions that rely on certain information from the reports being imported into their databases from the email recieved and a duplicate email is totally unacceptable

Thanks in advance,


Monday, June 03, 2013 - 7:36:03 AM - Eugene Back To Top

Just wanted to say thanks!  Really helped me a lot!

Monday, October 29, 2012 - 12:07:11 AM - Brahadesh Ramamurthy Back To Top




            Cast(b.name as Uniqueidentifier) 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 Cast(a.ScheduleID as nvarchar(50)) =CAST(b.name as nvarchar(50))


            JOIN ReportServer.dbo.ReportSchedule c


            ON Cast(b.name as nvarchar(50)) = cast(c.ScheduleID as nvarchar(50))


            JOIN ReportServer.dbo.Subscriptions d


            ON Cast(c.SubscriptionID as nvarchar(50)) = Cast(d.SubscriptionID as nvarchar(50))


            JOIN ReportServer.dbo.Catalog e


            ON Cast(d.report_oid as nvarchar(50)) = Cast(e.itemid as nvarchar(50))


    WHERE e.name = 'Sales'


Tuesday, October 16, 2012 - 6:08:50 AM - Mayan Back To Top

I changed the EXEC part in order to be able to rerun caching etc. which failed too:



'EXEC ReportServer.dbo.AddEvent @EventType=''' 

+ eventtype +''',@EventData='''+

+CAST(a.SubscriptionID ASVARCHAR(40))+''''AS ReportCommand

Monday, August 20, 2012 - 12:55:29 PM - Ludwig Guevara Back To Top

With the following sql statement will show you the definition for each report, if the report is using a store procedure or a SQL statement, Of course, If your Reporting Services instance has a different name, you'll need to change the bold name

--Find the procedure names that are being used along with report, datasource names
-- ==============================================================
DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition',
'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd )
SELECT  name
, x.value('CommandType[1]', 'VARCHAR(50)') AS CommandType
, x.value('CommandText[1]','VARCHAR(50)') AS CommandText
, x.value('DataSourceName[1]','VARCHAR(50)') AS DataSource
FROM ReportServer.dbo.Catalog
WHERE content is not null and TYPE = 2) a
CROSS APPLY reportXML.nodes('/Report/DataSets/DataSet/Query') r(x)
WHERE x.value('CommandType[1]', 'VARCHAR(50)') = 'StoredProcedure'


USE ReportServer

--Find the relationship between a report and a DataSet
-- ==============================================================
    c.Name [Object]
    , ( CASE [Type] WHEN 1 THEN 'Folder' WHEN 2 THEN 'Report' WHEN 3 THEN 'Resource'
             WHEN 4 THEN 'Linked Report' WHEN 5 THEN 'Data Source' WHEN 6 THEN 'Report Model' END ) [Object Type]  
    , c.Path, ds.Name [DataSource], ds.Link [DataSet Link]
FROM dbo.Catalog C (NOLOCK)
JOIN dbo.DataSource DS (NOLOCK) ON DS.ItemID = C.ItemID
WHERE c.Type NOT IN (1,5,6)



Thursday, August 02, 2012 - 1:42:57 PM - Priya Back To Top

Very helpful tip..Thanks for you help Sankar

Monday, July 23, 2012 - 4:45:26 AM - Mayan Back To Top

Your query really helped me track failed subscriptions and rerun them. (with the first 5 remarks updates too). Thanks a lot!

Monday, June 25, 2012 - 4:14:43 PM - aas4mis Back To Top

Always funny how somebody opens an old thread days before I need it. At any rate, I was getting conversion errors when I ran this (conversion failed when converting from a character string to uniqueidentifier). This is in the joins to b.name. Convert a.ScheduleID and C.ScheduleID to varchar for a proper join.

Hope this helps somebody.

Sankar, thanks for the code. I was really getting tired of searching for the scheduled time any time I wanted to force a job to run.



Tuesday, June 12, 2012 - 12:26:14 PM - Surbhi Back To Top

Hi Benjim,


Were u able to do maintain ubscriptions and related schedule for a reports?

I am currently working on same kind of project, where, I have to update schedule for a subscription of report using my application and update the tables of reportserver db from backend and ASP.Net.

Please share the details if you were able to complete your project.


Sunday, March 21, 2010 - 6:25:57 AM - benjim Back To Top

Since all the metadata is identified for a subscription report, is there a way to insert a subscription and a schedule into these tables without going through the SSRS pages.

I am working on a project were subscriptin details are maintained in user defined tables.


Any suggestions ?




Thursday, October 08, 2009 - 2:15:53 PM - SankarReddy Back To Top

You certainly don't want to do that. On a related note, here is the MSFT recommendation.

Here is a quote from BOL: 

The table structure for both databases is optimized for server operations and should not be modified or tuned. Microsoft might change the table structure from one release to the next. If you modify or extend the database, you might limit or prevent the capability to perform future upgrades or apply service packs. You might also introduce changes that impair report server operations.



Thursday, October 08, 2009 - 2:06:56 PM - erutledge Back To Top

This is great for figuring out what is what, but is there a way to permanently change the SQL Server agent Job Name?  Just by finding the name of the job, then updating the column in that table? Thanks!

Wednesday, September 30, 2009 - 1:14:59 PM - jerryhung Back To Top

I got duplicate on SOME reports with the original query posted

I think it's from the "old/deleted" subscription and the double-join between table a and c (ReportServer.dbo.ReportSchedule)


This one works for me

SELECT  'EXEC ReportServer.dbo.AddEvent @EventType=''TimedSubscription'', @EventData='''
        + CAST(a.SubscriptionID AS VARCHAR(40)) + '''' AS ReportCommand
       ,b.name AS JobName
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 a.SubscriptionID = d.SubscriptionID
        JOIN ReportServer.dbo.Catalog e ON d.report_oid = e.itemid
WHERE   1 = 1
        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.'
--e.name = 'Sales_Report'


Tuesday, September 29, 2009 - 12:44:07 AM - WelshGandalf Back To Top

 Thanks a lot, yes that's the setup I have and your fix make it work just fine now :)

Monday, September 28, 2009 - 11:28:30 AM - SankarReddy Back To Top
Hi Welsh, I am able to reproduce the problem you are facing. This happens when you install reporting services on the same box and you have other SQL Jobs created. To fix the error, you need to add an extra condition like below to the where clause. I have sent the update to Greg and hopefully the post will be updated.

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.'

Monday, September 28, 2009 - 8:04:31 AM - SankarReddy Back To Top

Hi Welsh,

If you don't mind, can you send me the version of SQL Server [use SELECT @@VERSION] and a screenshot if possible to my email id at SankarAThotmailDOTcom. We can post the findings once we identify the root cause here.


Monday, September 28, 2009 - 6:28:29 AM - WelshGandalf Back To Top

 This would be really useful to me but your code in part 2 sometimes gives some results (but not a full set) and always gives me the following error message...

Conversion failed when converting from a character string to uniqueidentifier.

Can you help with this? I'm unfamiliar with the tables in the ReportServer database so would find it difficult to diagnose the problem myself.

Learn more about SQL Server tools