Click here to Skip to main content
15,797,608 members
Articles / Programming Languages / SQL
Tip/Trick

Manually Run All Subscription Reports in SSRS At Once

Rate me:
Please Sign up or sign in to vote.
0.00/5 (No votes)
24 Aug 2013CPOL 12.7K   1   2
How to 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.

SQL
/*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

License

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

 
QuestionProblem with SSRS Authentica​tion - don't want to use Custom Authentica​tion - Please guide Pin
Member 1069685125-Mar-14 5:25
Member 1069685125-Mar-14 5:25 
SuggestionRe: Problem with SSRS Authentica​tion - don't want to use Custom Authentica​tion - Please guide Pin
Mubin M. Shaikh28-Mar-14 2:09
professionalMubin M. Shaikh28-Mar-14 2:09 
Hi Asif,

Is your SSRS installed in native mode? if yes then you can use integrated authentication mode with your data source and pass windows credentials for login.

or if your SSRS configured to support Form Authentication then you need to pass parameter as per your application users configured.

I need to know first how your ssrs configured?

Best Regards,
Mubin

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.