Click here to Skip to main content
15,794,629 members
Articles / Database Development / SQL Server / SQL Server 2008

Run All Subscription Reports At Once in SSRS Manually

Rate me:
Please Sign up or sign in to vote.
5.00/5 (3 votes)
5 Sep 2013CPOL2 min read 38.6K   88   5   5
Manually Run SSRS Scheduled Reports


Have you ever seen your Subscription/ Scheduled reports in Microsoft SSRS (SQL Server Reporting Services) not fired on time due to any unexpected failure on your Server and Client asked to run them and send Reports to Scheduled Mail IDs??

Let us see first Procedure, this is really a very tedious task, but still very helpful.

Find GUID of every scheduled report in SQL Server Management Studio using T-SQL query.

Using the Code to Run Subscribed Reports One By One

/*Connect to Database ReportServer in SSMS and run the below query to find SQL agent JobID for SSRS Report*/

  1. Open SQL Server Management Studio
  2. Connect to the Report Server
  3. Choose ReportServer for the database
  4. Execute the following script to find all the SQLAgent Job Name (Job ID), and run them all using Cursor
  5. Specify Where condition to locate specific reports
Use ReportServer GO 
SELECT S.ScheduleID AS SQLAgent_Job_Name ,SUB.Description AS Sub_Desc ,_
SUB.DeliveryExtension AS Sub_Del_Extension ,C.Name AS ReportName ,_
C.Path AS ReportPathFROM ReportSchedule RS INNER JOIN Schedule S ON _
(RS.ScheduleID = S.ScheduleID) INNER JOIN Subscriptions SUB _
ON (RS.SubscriptionID = SUB.SubscriptionID) INNER JOIN [Catalog] C _
ON (RS.ReportID = C.ItemID AND SUB.Report_OID = C.ItemID)WHERE C.Name _
LIKE 'xxx' --Enter Report Name to find Job_Name

Now run SQL Agent Job using Stored Procedure one by one and pass jobname of Report which you get using the above query.

USE msdb EXEC sp_start_job @job_name = " –Enter SQLAgent_Job_Name

I think every programmer hates this boring procedure to find GUIDS of every report scheduled using subscription facility and then locate it in SQL Agent or Run it... or run them manually in SQL Server management Studio.

This SQL Script will Find All Scheduled Reports in SSRS2008R2 and Run all of them at once, so all reports will be rendered to its format set previously in your subscription and relevant emails will also be fired with attachment.

In other words, this will be helpful in the execution of SSRS Report Subscriptions manually. The following code will allow the report developer to manually execute a report subscription if the subscription has failed.

Using the Code to Run All Subscribed Reports At Once

  1. Open SQL Server Management Studio
  2. Connect to the Report Server
  3. Choose ReportServer for the database
  4. Execute the following script to find all the SQLAgent Job Name (Job ID), and run them All using Cursor
  5. Specify Where condition to locate specific Reports

Now run all subscribed reports at once.

/*Created by Mubin M. Shaikh*/
--Your Report Server Name
use ReportServer
DECLARE @ScheduledReportName varchar(200)
DECLARE @JobID uniqueidentifier
DECLARE @LastRunTime datetime
Declare @JobStatus Varchar(100)
CAT.[Name] AS RptName
, res.ScheduleID AS JobID
, sub.LastRuntime
, CASE WHEN job.[enabled] = 1 THEN 'Enabled'
ELSE 'Disabled'
END AS JobStatus

dbo.Catalog AS cat
INNER JOIN dbo.Subscriptions AS sub
ON CAT.ItemID = sub.Report_OID
INNER JOIN dbo.ReportSchedule AS res
ON CAT.ItemID = res.ReportID
AND sub.SubscriptionID = res.SubscriptionID
INNER JOIN msdb.dbo.sysjobs AS job
ON CAST(res.ScheduleID AS VARCHAR(36)) = job.[name]
INNER JOIN msdb.dbo.sysjobschedules AS sch
ON job.job_id = sch.job_id
INNER JOIN dbo.Users U
ON U.UserID = sub.OwnerID
--specify your search criteria here to find reports if any specific 
--where CAT.[Name] like '%Daily%' and convert(date,sub.LastRunTime ) >convert(date,GETDATE()-2)
ORDER BY U.UserName, RptName 
OPEN @RunAllReport
INTO @ScheduledReportName,@JobID,@LastRunTime,@JobStatus
Print @ScheduledReportName --&' ' & @JobID
EXEC msdb.dbo.sp_start_job @job_name =@JobID
INTO @ScheduledReportName,@JobID,@LastRunTime,@JobStatus
CLOSE @RunAllReport

Enjoy T-SQLization.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Written By
Architect Cybage Software Pvt. Ltd.
India India
Microsoft® Certified Professional (Microsoft Certification ID: 8918672).

Microsoft Certified Technology Specialist with more than 16+ years of expertise to architect and implement effective solutions for Data Analytics, Reporting and Data Visualization solutioning need on Azure Cloud or On-Premise

Technology :
Azure (Data Lake, Data Factory, Synapse Analytics, Databricks, SQL),
Microsoft BI (SSIS, SSAS, SSRS, SQL-Server), C#.Net, Pentaho,
Data Warehousing, Dimension modelling, Snowflake DW, SQL DW, MySQL
Data Visualization using (Tableau, Power BI, QlikView, Pentaho),
Domain : Sales, Retail, CRM, Public Transport, Media & Entertainment, Insurance
Data Integration and Analytics Experience with MS. Dynamic CRM, Salesforce CRM, Dataverse, SAP- FI, Dynamics AX etc.

Linked In Profile:
Click Here to View Linked In Profile

Change will not come if we keep waiting for some other person !!, or keep waiting for some other time !!, We are the one we are waiting for, We are the change that we are looking for.

Comments and Discussions

QuestionLife saver but under which credentials are then used to run the subscriptions? Pin
Member 1263058012-Jul-16 0:20
Member 1263058012-Jul-16 0:20 
QuestionVery Helpful Pin
javidhkhan18-Feb-15 6:26
javidhkhan18-Feb-15 6:26 
GeneralMy vote of 5 Pin
Suket shah18-Sep-13 1:04
professionalSuket shah18-Sep-13 1:04 
its saved a lot of time.
QuestionSame SQL JobID's but different SubscriptionID Pin
tudorza27-Aug-13 22:36
tudorza27-Aug-13 22:36 
AnswerRe: Same SQL JobID's but different SubscriptionID Pin
Mubin M. Shaikh28-Aug-13 7:14
professionalMubin M. Shaikh28-Aug-13 7:14 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.