Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

migrated from superuser.com Mar 12 '10 at 15:06

This question came from our site for computer enthusiasts and power users.

4 Answers 4

up vote 4 down vote accepted

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.

share|improve this answer
    
+1'd. That's way better than my idea :-) –  Chris_K Mar 13 '10 at 3:39

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?

share|improve this answer

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.

share|improve this answer

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.