Schedule a job that misses specific dates?

[lambic]

Hi,

We have some SQL Server 2000 jobs set up to run under the SQL Server Agent each weekday & I was wondering whether it's possible to configure them not to run on specific dates. The reason being is that we don't want them to run on bank holidays.
We currently manually un-check the day on the schedule as & when required.

Many Thanks

 

[tlbroadbent]

I don't know of any way to schedule exception days in the job schedule. You could create a stored procedure that disables/enables the job automatically depending on the date. Use sp_update_jobschedule to disable/enable a job.
[tt]
USE msdb

--Assuming there is a calendar table...
--If holiday and disable job.
If Exists
(Select * From Calendar
Where CalDate = convert(char(10), getdate(), 120)
And CalHoliday=1)

EXEC sp_update_jobschedule
@job_name = 'Your Job Name',
@enabled = 0
[/tt]

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[lambic]

Thanks Terry, I'll give it a go!