Manually Run All Subscription Reports in SSRS At Once

Introduction

This script can be used to run all scheduled reports you have scheduled using subscription facility available in Microsoft SSRS 2008/2008 r2(SQL Server reporting services) manually in one shot.

When I searched on the internet using Google, I did not find any help on running all scheduled reports manually at once, so I created this script which can help many, when they have any issues on SSRS report Server and scheduled reports on that day are not sent due to any reason.

So using the given script, admin can run this in SQL Server Management Studio to fire all scheduled reports manually.

/*Created by Mubin M. Shaikh. M.+919824067097 /mubin4all@gmail.com (Sr.Software Engineer @ Cygnet Infotech ) */

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 top 1
 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
 
--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