Enable/Disable jobs

This post will probably not have mass appeal, but it's something I wrote recently that I find of value. The situation I was working with is that on our OLAP server, we have a number of "Invoke" agent jobs that fire off ever 2 minutes which go out and look to see if we need to process run our ETL's and process our cube. When we are doing releases or work on ETL's we want to disable all the Invoke jobs so they don't start. I'm sure you can think of other situations where this might be necessary outside of cube processing.

So one way to do this (arguably the faster way) is to open your job manager and highlight the invoke jobs and right-click and choose disable. But what if you have a few invokes disabled for one reason or another? You'll have to either remember which ones were disabled, or make a list, and make sure you don't re-enable those when you are done. Or, what if you want to wait for your current Invoke to end before you do the disable (not necessary, but what if), or you want to do it at a specific time of day?

I came up with a script that I put into two agent jobs to facilitate this. These can be scheduled and work pretty well.

The first job, creates a storage table, stores the id's for the enabled jobs, then disables them. (It checks to see if the job exists first and if so, error's out)

/*******************************
** Created By: Eric Zierdt   **
** ericzierdt@gmail.com      **
** ericemployed.blogspot.com **
*******************************/

IF OBJECT_ID('msdb.dbo.ActiveInvokeJobs') IS NULL
BEGIN
 CREATE TABLE FRZ_ADMINDB.dbo.ActiveInvokeJobs (
    job_id UNIQUEIDENTIFIER
  , [Name] VARCHAR(255)
 )
 INSERT INTO msdb.dbo.ActiveInvokeJobs
 ( job_id, [Name])
 SELECT  job_id
     ,[Name]
 FROM    msdb.dbo.sysjobs S
 WHERE   enabled = 1
   AND name LIKE '%invoke%'

 UPDATE msdb.dbo.sysjobs
 SET  enabled = 0
 WHERE job_id IN (
    SELECT  job_id
    FROM msdb.dbo.ActiveInvokeJobs
   )
END
ELSE
BEGIN
 RAISERROR ('Storage Table Already Exists.',16,1);
END

The next agent job checks to see if the storage table exists, and if so enables the disabled jobs, then deletes the storage table.

/*******************************
** Created By: Eric Zierdt   **
** ericzierdt@gmail.com      **
** ericemployed.blogspot.com **
*******************************/

IF OBJECT_ID('msdb.dbo.ActiveInvokeJobs') IS NOT NULL
BEGIN
 UPDATE msdb.dbo.sysjobs
 SET  enabled = 1
 WHERE job_id IN (
    SELECT  job_id
    FROM msdb.dbo.ActiveInvokeJobs
   )

 DROP TABLE msdb.dbo.ActiveInvokeJobs

END
ELSE
BEGIN
 RAISERROR ('Storage Table Does Not Exist.',16,1);
END