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*/
- Open SQL Server Management Studio
- Connect to the Report Server
- Choose
ReportServer
for the database - Execute the following script to find all the
SQLAgent
Job Name (Job ID), and run them all using Cursor - 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'
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
- Open SQL Server Management Studio
- Connect to the Report Server
- Choose
ReportServer
for the database - Execute the following script to find all the SQLAgent Job Name (Job ID), and run them All using Cursor
- Specify
Where
condition to locate specific Reports
Now run all subscribed reports at once.
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
ORDER BY U.UserName, RptName
OPEN @RunAllReport
FETCH NEXT FROM @RunAllReport
INTO @ScheduledReportName,@JobID,@LastRunTime,@JobStatus
WHILE @@FETCH_STATUS = 0
BEGIN
Print @ScheduledReportName
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.