Click here to Skip to main content
Click here to Skip to main content
Go to top

Run All Subscription Reports At Once in SSRS Manually

, 5 Sep 2013
Rate this:
Please Sign up or sign in to vote.
Manually Run SSRS Scheduled Reports

Introduction

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
go
DECLARE @ScheduledReportName varchar(200)
DECLARE @JobID uniqueidentifier
DECLARE @LastRunTime datetime
Declare @JobStatus Varchar(100)
--------------------------------------------------------
DECLARE @RunAllReport CURSOR
SET @RunAllReport = CURSOR FAST_FORWARD
FOR
SELECT 
CAT.[Name] AS RptName
, res.ScheduleID AS JobID
, sub.LastRuntime
, CASE WHEN job.[enabled] = 1 THEN 'Enabled'
ELSE 'Disabled'
END AS JobStatus

FROM
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
FETCH NEXT FROM @RunAllReport
INTO @ScheduledReportName,@JobID,@LastRunTime,@JobStatus
WHILE @@FETCH_STATUS = 0
BEGIN
Print @ScheduledReportName --&' ' & @JobID
EXEC msdb.dbo.sp_start_job @job_name =@JobID
FETCH NEXT FROM @RunAllReport
INTO @ScheduledReportName,@JobID,@LastRunTime,@JobStatus
END
CLOSE @RunAllReport
DEALLOCATE @RunAllReport 

Enjoy T-SQLization.

License

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

Share

About the Author

Mubin M. Shaikh
Team Leader
India India
Microsoft® Certified Professional (Microsoft Certification ID: 8918672).

Design and Develop Business Intelligence Solutions using Microsoft BI.
(SQL Integration Services - SSIS, SQL Analysis Services - SSAS, Reporting Services - SSRS,SQL-Server,Dimension Modelling,Data Warehouse,Power Pivot, Power View, Power Map, Power query,.Net,C#,WCF)

Linked In Profile:
 
Click Here to View Linked In Profile
 
Change Will Not Come If We Wait for Some Other Person,or Wait for Some Other Time, We are the One We are Waiting For,We are the Change That we Seek.
Follow on   LinkedIn

Comments and Discussions

 
GeneralMy vote of 5 PinmemberMember 1024748318-Sep-13 0:04 
QuestionSame SQL JobID's but different SubscriptionID Pinmembertudorza27-Aug-13 21:36 
AnswerRe: Same SQL JobID's but different SubscriptionID [modified] PinprofessionalMubin M. Shaikh28-Aug-13 6:14 

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

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

| Advertise | Privacy | Mobile
Web02 | 2.8.140916.1 | Last Updated 5 Sep 2013
Article Copyright 2013 by Mubin M. Shaikh
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid