Run All Subscription Reports At Once in SSRS Manually

• Download script - 1.2 KB

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.