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!

Related Questions

Virtualbox shared clipboard string size limit

1  Asked on February 23, 2021 by kshitiz-sharma


Where is WSGI installed on Centos?

2  Asked on February 21, 2021 by petey


Enabling X-Spam-Report in SpamAssassin

0  Asked on February 19, 2021 by letmesothat4u


graphite queries and confusion around time

1  Asked on February 19, 2021 by aditya-patawari


2 versions of libssl on one system

1  Asked on February 18, 2021 by jakub-urbanowicz


Azure Virtual Gateway not using Route table

1  Asked on February 17, 2021 by hein-gustavsen


SSSD+Samba+SSH GSSAPI authentication issues

1  Asked on February 17, 2021 by eroji


How to add permissions to windows user in samba on ubuntu server

2  Asked on February 17, 2021 by matthew-tranmer


SSL Test for the server domain (not a web app domain)

0  Asked on February 17, 2021 by mekswoll


How to set up QoS on MikroTik RouterOS?

1  Asked on February 16, 2021 by udo-g


Ask a Question

Get help from others!

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