SQL Server Agent Job - Schedule daily except 1 day per month

Server Fault Asked by Shevek on August 29, 2020

We need to run a daily job using SQL Server Agent (SQL 2000) but we don’t want it to run on the 22nd of each month as there are other processes which only run on that day which will clash.

The recurring settings in Agent do not provide any exclusion options, is there any way to achieve this?

5 Answers

Put a job step as the first step in the job to look to see if the DATEPART(dd, getdate()) = 22 and if it does issue an sp_stopjob to stop the job.

Correct answer by mrdenny on August 29, 2020

I wrapped an if statement around my whole procedure

if RIGHT( convert(varchar(11),CURRENT_TIMESTAMP,120),2) = 22 do something else end

Answered by JGibson on August 29, 2020

Have a 2nd job run on the 22nd that disables the first job:

exec msdb..sp_update_job @job_id = 0xC0923E444828064EA22B46B2C47A1363, @enabled = 0

And then on the 23rd re-enable the job:

exec msdb..sp_update_job @job_id = 0xC0923E444828064EA22B46B2C47A1363, @enabled = 1

Answered by Shoeless on August 29, 2020

Well, one option is similar to your existing idea. Create another job that runs a script to check the date. If it is the 22nd that job then disables your daily job (via SQL). If it isn't the 22nd it enables your daily job.

The only advantage this might have over your suggestion is that it keeps your daily job "pure" of any date logic.

Answered by Chris_K on August 29, 2020

One way to do this is to include a check for date in the script to be run and not run it on the 22nd.

Is this the only way though?

Answered by Shevek on August 29, 2020

Add your own answers!

Ask a Question

Get help from others!

© 2024 All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP