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