Scheduling jobs to run daily, with exceptions

Here is a very simple way to schedule a job to run daily (or any other time interval) and not run on a specific day (a local holiday, etc).

The key here is to exploit the 'go to step' feature combined with the 'raiserror' stmt. The following SQL job will run its second step any day as scheduled, except on April 25th.

---
-- Script generated on 4/30/2002 3:09 PM
-- By: PCVISINTINAdministrator
-- Server: PCVISINTINPCVISINTINPROD

BEGIN TRANSACTION            
  DECLARE @JobID BINARY(16)  
  DECLARE @ReturnCode INT    
  SELECT @ReturnCode = 0     

IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]') < 1 
  EXECUTE msdb.dbo.sp_add_category 
          @name = N'[Uncategorized (Local)]'

  -- Delete the job with the same name (if it exists)
  SELECT @JobID = job_id     
  FROM   msdb.dbo.sysjobs    
  WHERE (name = N'Runs daily except single holiday on apr 25th')       
  IF (@JobID IS NOT NULL)    
  BEGIN  

  -- Check if the job is a multi-server job  
  IF (EXISTS (SELECT  * 
              FROM    msdb.dbo.sysjobservers 
              WHERE   (job_id = @JobID) AND (server_id <> 0))) 
  BEGIN 

    -- There is, so abort the script 
    RAISERROR (N'Unable to import job ''Runs daily except single holiday on apr 25th'' since there is already a multi-server job with this name.', 16, 1) 
    GOTO QuitWithRollback  
  END 
  ELSE 

    -- Delete the [local] job 
    EXECUTE msdb.dbo.sp_delete_job 
            @job_name = N'Runs daily except single holiday on apr 25th' 
    SELECT @JobID = NULL
  END 

BEGIN 

  -- Add the job
  EXECUTE @ReturnCode = msdb.dbo.sp_add_job 
          @job_id = @JobID OUTPUT , 
          @job_name = N'Runs daily except single holiday on apr 25th',
          @owner_login_name = N'PCVISINTINAdministrator', 
          @description = N'No description available.', 
          @category_name = N'[Uncategorized (Local)]', 
          @enabled = 1, 
          @notify_level_email = 0, 
          @notify_level_page = 0, 
          @notify_level_netsend = 0, 
          @notify_level_eventlog = 2, 
          @delete_level= 0
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

  -- Add the job steps
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep 
          @job_id = @JobID, 
          @step_id = 1, 
          @step_name = N'CheckHoliday', 
          @command = N'IF month(getdate())=4 and day(getdate())=25
 begin
    raiserror(''holiday'',16, 1)
    end
         ', @database_name = N'master', 
         @server = N'', 
         @database_user_name = N'', 
         @subsystem = N'TSQL', 
         @cmdexec_success_code = 0, 
         @flags = 0, 
         @retry_attempts = 0, 
         @retry_interval = 1, 
         @output_file_name = N'', 
         @on_success_step_id = 0, 
         @on_success_action = 3, 
         @on_fail_step_id = 3, 
         @on_fail_action = 4
  IF (@@ERROR <>  0 OR @ReturnCode <>  0) GOTO QuitWithRollback 

  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep 
          @job_id = @JobID, 
          @step_id = 2, 
          @step_name = N'Execute always but apr 25th',  
          @command = N'select getdate(),''not holiday''', 
          @database_name = N'master', 
          @server = N'', 
          @database_user_name = N'', 
          @subsystem = N'TSQL', 
          @cmdexec_success_code = 0, 
          @flags = 0, 
          @retry_attempts = 0, 
          @retry_interval = 1, 
          @output_file_name = N'', 
          @on_success_step_id = 0, 
          @on_success_action = 3, 
          @on_fail_step_id = 0, 
          @on_fail_action = 3
  IF (@@ERROR <>  0 OR @ReturnCode <>  0) GOTO QuitWithRollback 

  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep 
          @job_id = @JobID, 
          @step_id = 3, 
          @step_name = N'EndOfJob', 
          @command = N'select ''finished''', 
          @database_name = N'master', 
          @server = N'', 
          @database_user_name = N'', 
          @subsystem = N'TSQL', 
          @cmdexec_success_code = 0, 
          @flags = 0, 
          @retry_attempts = 0, 
          @retry_interval = 1, 
          @output_file_name = N'', 
          @on_success_step_id = 0, 
          @on_success_action = 1, 
          @on_fail_step_id = 0, 
          @on_fail_action = 2
  IF (@@ERROR <>  0 OR @ReturnCode <>  0) GOTO QuitWithRollback 
  EXECUTE @ReturnCode = msdb.dbo.sp_update_job 
          @job_id = @JobID, 
          @start_step_id = 1 

  IF (@@ERROR <>  0 OR @ReturnCode <>  0) GOTO QuitWithRollback 

  -- Add the Target Servers
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver 
          @job_id = @JobID, 
          @server_name = N'(local)' 
  IF (@@ERROR <>  0 OR @ReturnCode <>  0) GOTO QuitWithRollback 

END
COMMIT TRANSACTION          
GOTO   EndSave              
QuitWithRollback:
  IF (@@TRANCOUNT >  0) ROLLBACK TRANSACTION 
EndSave: 

For More Information

• What do you think about this tip? E-mail the Editor at tdichiara@techtarget.com with your feedback.
• The Best SQL Server Web Links: tips, tutorials, scripts, and more.
• Have an SQL Server tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
• Ask your technical SQL Server questions--or help out your peers by answering them--in our live discussion forums.
• Check out our Ask the Experts feature: Our SQL, Database Design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.