Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips













































   Got a SQL tip?
            We want to know!

How to easily identify a scheduled SQL Server Reporting Services report

MSSQLTips author Sankar Reddy By:   |   Read Comments (19)   |   Related Tips: > 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.

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.


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:



Last Update: 9/28/2009


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
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Comments and Feedback:
Monday, September 28, 2009 - 6:28:29 AM - WelshGandalf Read The Tip

 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.


Monday, September 28, 2009 - 8:04:31 AM - SankarReddy Read The Tip

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 - 11:28:30 AM - SankarReddy Read The Tip
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.'

Tuesday, September 29, 2009 - 12:44:07 AM - WelshGandalf Read The Tip

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


Wednesday, September 30, 2009 - 1:14:59 PM - jerryhung Read The Tip

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
 


Thursday, October 08, 2009 - 2:06:56 PM - erutledge Read The Tip

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!


Thursday, October 08, 2009 - 2:15:53 PM - SankarReddy Read The Tip

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

 


Sunday, March 21, 2010 - 6:25:57 AM - benjim Read The Tip

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 

 


Tuesday, June 12, 2012 - 12:26:14 PM - Surbhi Read The Tip

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.

 


Monday, June 25, 2012 - 4:14:43 PM - aas4mis Read The Tip

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


Monday, July 23, 2012 - 4:45:26 AM - Mayan Read The Tip

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


Thursday, August 02, 2012 - 1:42:57 PM - Priya Read The Tip

Very helpful tip..Thanks for you help Sankar


Monday, August 20, 2012 - 12:55:29 PM - Ludwig Guevara Read The Tip

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)

 

 


Tuesday, October 16, 2012 - 6:08:50 AM - Mayan Read The Tip

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, October 29, 2012 - 12:07:11 AM - Brahadesh Ramamurthy Read The Tip

 

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'

          


Monday, June 03, 2013 - 7:36:03 AM - Eugene Read The Tip

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


Wednesday, June 05, 2013 - 5:34:10 AM - Nevarda Read The Tip

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


Friday, July 05, 2013 - 5:15:44 AM - Carnalito Read The Tip

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 18, 2014 - 2:40:04 PM - Gord Cross Read The Tip

Thanks very much, great tip! 



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.