sql-serversql-agent-job

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.

Solution

  • 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