Pause, Resume and Run a Reporting Services SharePoint Shared Schedule
August 1, 2014 Leave a comment
I’ve recently re-entered the world of SharePoint Reporting Services integration and needed a way to run a shared schedule from a SQL job.
The scenario is this – We have a job that runs in the morning to populate the data warehouse, after the data warehouse it populates the cubes. What I want to happen next is for a number of reports to run against the cubes to warm up the SSAS cache and wake up reporting services ready for the business users.
So I have created the stored proc below to do just that – to make it a bit more useful I have also built in the ability to enable or disable a shared schedule. That way if the morning load fails we can programmatically disable the daily reports so they don’t go out with missing data.
Here’s the sproc….
USE ReportServer
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_Control_SharedSchedules]
(
@Schedule varchar(200)
,@Action varchar(8)
)
AS
BEGIN
/*
============================================================
= usp_Control_SharedSchedules 'Runs After Cube Processing Complete', 'Enable'
============================================================
= Description
= ------------------
= Author : Dan Wakefield
= Created Date : 1 Aug 2014
=
= History
= ------------------
= Created to allow us to kick off a set of reports to warm the cache
= after the cubes have finished processing.
=
= Can also be used to disable a schedule if the load fails and enable it again after it succeeds.
=
= Input
= --------
= @ScheduleName = Name of shared schedule e.g. 'Daily 10am'
= @Action = What do you want to happen? Disable, Enable or Run
(enable means it will run next time it reaches its usual scheduled time, run means run now)
=
= Return
= ----------
= RS
============================================================
*/
SET DATEFIRST 1
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
/*
===============================================================================================
= Declarations
===============================================================================================
*/
DECLARE @job_name VARCHAR(128)
SELECT @job_name = ScheduleID
FROM Schedule
WHERE Name = @Schedule
/*
===============================================================================================
= Processing Code
===============================================================================================
*/
IF @Action = 'Run'
BEGIN
-- Run a shared schedule
-- This will kick off all reports that are tied to the named shared schedule
EXEC msdb.dbo.sp_start_job @job_name
END
IF @Action = 'Disable'
BEGIN
-- Disable schedule
-- No reportts tied to this schedule will run until re-enabled
UPDATE MSDB.dbo.sysjobs
SET Enabled = 0
WHERE Name = @job_name;
END
IF @Action = 'Enable'
BEGIN
-- Enable schedule
-- All reports tied to this schedule will run next time the scheduled time arrives
UPDATE MSDB.dbo.sysjobs
SET Enabled = 1
WHERE Name = @job_name;
END
END
Dan's BI Braindump 