How to easily identify a scheduled SQL Server Reporting Services report

By:   |   Updated: 2009-09-28   |   Comments (24)   |   Related: > Reporting Services Monitoring


Problem

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.

Solution

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.

databases

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. 

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

filter settings

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. 

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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



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.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2009-09-28

Comments For This Article




Friday, January 10, 2020 - 2:22:04 PM - Kevin Archibald Back To Top (83675)

Sometimes msdb.dbo.sysjobs isn't a guid, so you should change that join to add b.category_id=100

 JOIN msdb.dbo.sysjobs b ON a.ScheduleID = b.name AND b.category_id = 100

Wednesday, October 11, 2017 - 10:41:36 AM - Hiram Back To Top (67201)

 

Simplified and fixed conversion error. This gives the start job command as well based on Path, commonly found on ExecutionLog2, instead of Name.

 

USE ReportServer

go

select ReportPath, Parameters, Status, TimeStart from ReportServer.dbo.ExecutionLog2 where status <>'rsSuccess' and timestart>getdate()-1

go

SELECT  'exec msdb.dbo.sp_start_job @job_name=''' + + CAST(a.ScheduleID AS VARCHAR(40)) + ''''  as StartJobCommand

,'exec ReportServer.dbo.AddEvent @EventType=''TimedSubscription'', @EventData=''' + CAST(a.SubscriptionID AS VARCHAR(40)) + '''' AS ReportCommand

FROM ReportServer.dbo.ReportSchedule a 

JOIN ReportServer.dbo.Subscriptions d

ON a.SubscriptionID = d.SubscriptionID

JOIN ReportServer.dbo.Catalog e

ON d.report_oid = e.itemid

WHERE e.Path in ( '/Accounting/Management/EOMReport', '/Sales/Management/EOMReport' )

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

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
,a.SubscriptionID
,e.NAME
,e.Path
,d.Description
,d.LastStatus
,d.EventType
,d.LastRunTime
,b.date_created
,b.date_modified
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 2, 2014 - 1:31:04 PM - Lisa Back To Top (34823)

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 (34512)

 

Hi,

 

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

 

Thanks

Sirisha

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

Thanks very much, great tip! 

Friday, July 5, 2013 - 5:15:44 AM - Carnalito Back To Top (25720)

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.

 

Regards,

Carnalito

Wednesday, June 5, 2013 - 5:34:10 AM - Nevarda Back To Top (25302)

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,

Nevarda

Monday, June 3, 2013 - 7:36:03 AM - Eugene Back To Top (25251)

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

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

 

SELECT

 

            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 (19936)

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

SELECT

 

'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 (19130)

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
-- ==============================================================
WITH XMLNAMESPACES (
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 ( SELECT name, CAST(CAST(content AS VARBINARY(MAX)) AS XML) AS reportXML
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
-- ==============================================================
SELECT --TOP 100 PERCENT
    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 2, 2012 - 1:42:57 PM - Priya Back To Top (18891)

Very helpful tip..Thanks for you help Sankar

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

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 (18194)

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.

BTW,
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.

 

--aas4mis

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

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 (5090)

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 ?

 

Jimmy 

 

Thursday, October 8, 2009 - 2:15:53 PM - SankarReddy Back To Top (4162)

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.

http://technet.microsoft.com/en-us/library/ms156016.aspx

 

Thursday, October 8, 2009 - 2:06:56 PM - erutledge Back To Top (4160)

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 (4114)

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

--http://www.mssqltips.com/tip.asp?tip=1846
SELECT  'EXEC ReportServer.dbo.AddEvent @EventType=''TimedSubscription'', @EventData='''
        + CAST(a.SubscriptionID AS VARCHAR(40)) + '''' AS ReportCommand
       ,b.name AS JobName
       ,a.SubscriptionID
       ,e.name
       ,e.path
       ,d.description
       ,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 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'
ORDER BY LastRunTime DESC

RETURN
 

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

 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 (4100)
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 (4099)

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 (4098)

 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.














get free sql tips
agree to terms