Link to home
Start Free TrialLog in
Avatar of raterus
raterusFlag for United States of America

asked on

Return a Success/Failure to a Job based on a SQL query?

Here's my problem,

My company enables/disables SQL Jobs every holiday of the year.  Certain jobs can't run on holidays because they won't work.

I'd like to insert a step into this job that runs a query against a table (a holiday table), and if it finds the date, return a failure to the job (to which I'll end the job reporting success).

I can do all the math stuff, what I'm unsure about is how to return this success/failure to the Job execution from a sql statement.  I could write a console program to bomb on me, but that's too many layers for something so simple.

Any ideas?
Avatar of Shannon_Lowder
Shannon_Lowder
Flag of United States of America image

The simplest way to do this would be to create a table in your master/md database that stores not only official holidays, but those that your company may take off that are not national or banking holidays.  YOu could simply select getdate() and compare it to this table, and when it's found... don't run the job.

if (select count(*) from md.dbo.holidays where date = getdate()) < 1
begin
   job here
end

Avatar of raterus

ASKER

I need a way to stop a job once it's been started.  The job is scheduled like a normal job, so I don't have a place where I could code this in.  Maybe you know something I don't know here?
Does the job run non-stop? Or is it scheduled to run once or more per day?
Avatar of raterus

ASKER

It runs every weekday, except holidays of course.  The "except holidays", is manually changed.
ASKER CERTIFIED SOLUTION
Avatar of David Todd
David Todd
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
OK, I think i have your solution.  In the code that actually runs the job. If you are on SQL 7,2000 use Enterprise Manager, if you are on 2005 use sql management studio.

In Enterprise manager Under your server, click management, sql server agent, jobs, then highlight your job, then right click and hit properties.  Go to the steps tab, click on the step you are trying to make honor holidays, and click edit.

The command box should now be visible.

You should see the code that actually gets run when your job is executed.  Wrap all that code in the code i sent earlier.

if (select count(*) from md.dbo.holidays where date = convert(varchar, getdate(), 101)) < 1
begin
   job here
end

This does require you have the table md.dbo.holidays which has at least the column date in it.  You enter all the dates you want to honour as a holiday into this table. When the job runs, it will take todays date
(dropping the timestamp) and look it up in the table.  If it finds it the count will be 1, and therefore the job does nothing.  If it is not in there the count will be 0, and the job will run normally.

If you need directions for sql2005, I can give you those as well.

Also, if you are only executing a single stored proc, you could put the date code in the top of that procedure.  It would give you the same result.  You could also code a simple webpage with nothing more than a calendar, to allow management to select the dates they want to set as a holiday.  Takes the management burden off of you.

Let me know if you need anything further.
Avatar of raterus

ASKER

@Shannon_Lowder,

Thanks for spending the time to write out that response, but again, I don't think it's going to work.  My jobs have multiple steps, so even if I stop the first step from executing, I'm not about to, nor is it feasible to do this for every step.

@dtodd

I'm experimenting with Raiserror, I don't see why it won't work here.
Avatar of shadowsaint
shadowsaint

in your step do your date check then do something like:

IF (date check logic) -- If Holiday!
BEGIN
      SET   @msg = 'Holiday!!'
      RAISERROR( @msg, 16, 1 )
      RETURN -1
END

set the step to fail with success or whatever else you need to do.
Avatar of raterus

ASKER

Finally got around to finishing this, RAISERROR worked great!
I'm trying to cancel/stop a step/job from running if it runs past 7 a.m., any ideas about that?
Avatar of raterus

ASKER

Hi dawicki,

Try asking a related question, though I think the solution would be quite a bit different that what I did on this question.

https://www.experts-exchange.com/Database/MS-SQL-Server/newQuestionWizardRelated.jsp?qid=23094297