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?
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
2 Asked on February 25, 2021 by oliver-giesen
1 Asked on February 24, 2021 by spm
1 Asked on February 23, 2021 by kshitiz-sharma
1 Asked on February 22, 2021 by nona
2 Asked on February 21, 2021 by petey
2 Asked on February 20, 2021
0 Asked on February 20, 2021 by akash-pandey
0 Asked on February 19, 2021 by letmesothat4u
1 Asked on February 19, 2021 by aditya-patawari
1 Asked on February 18, 2021 by jakub-urbanowicz
0 Asked on February 18, 2021 by rusty009
0 Asked on February 18, 2021 by fryfubar
1 Asked on February 17, 2021 by oligofren
1 Asked on February 17, 2021 by hein-gustavsen
1 Asked on February 17, 2021 by eroji
2 Asked on February 17, 2021 by matthew-tranmer
0 Asked on February 17, 2021 by mekswoll
Get help from others!