CODEWITHSUNDEEP
sql-serversql-agent-job
Taryn
Taryn

Reputation: 247860

SQL Jobs Schedule to Not Run on Holidays

I have a SQL Agent Job that has multiple steps which are scheduled to run Monday - Friday at a certain time. I need to be able to implement a stop feature on this job to not run on Holidays that are listed in a table.

I don't really know how to proceed with this. Do I need to create a first step that checks if it is a holiday and then fails the job if it is?

I have a stored procedure that will check the date that I pass to see if it is a holiday, I just don't know how to force it to report failure if the result is yes it is a holiday. Any help would be greatly appreciated.

Upvotes: 5

Views: 6170

Answers (1)

gbn
gbn

Reputation: 432657

Idea:

  1. SQL Server Agent runs job
  2. 1st step is "check for holiday"
  3. code throws error
  4. job step silently fails

Point 3: To get the error from from the stored procedure to SQL Server Agent, you use RAISERROR

...
IF EXISTS (SELECT * FROM Holidays WHERE Date = GETDATE())
   RAISERROR ('Do nothing: relax: chill out', 16, 1);
...

Points 4: In this case, use "Quit with success" (1) for the @on_fail_action parameter to sp_add_jobstep

Upvotes: 6

Related Questions