raterus
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?
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?
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?
ASKER
It runs every weekday, except holidays of course. The "except holidays", is manually changed.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
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.
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.
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.
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.
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?
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
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
if (select count(*) from md.dbo.holidays where date = getdate()) < 1
begin
job here
end