January 11, 2012 at 10:49 am
I need to figure out how to cause a job to fail and do an alert based on whether a query returns rows from a select statement. I just need a basic example of how to do this. is there a particular return value that will signal to the sql job to fail?
January 11, 2012 at 10:51 am
I just force it to fail. SELECT * FROM tableThatDoesNotExist
Jared
CE - Microsoft
January 11, 2012 at 12:53 pm
I'm not really testing to see if a table exists or not but if rows in a table exist:
and I figured it out here is my answer for my question:
IF @@ROWCOUNT = 0
BEGIN
RAISERROR('You need to sync contactcalldetail.', 16, 1)
RETURN -100
END
January 11, 2012 at 12:55 pm
DeanORenO (1/11/2012)
I'm not really testing to see if a table exists or not but if rows in a table exist:and I figured it out here is my answer for my question:
IF @@ROWCOUNT = 0
BEGIN
RAISERROR('You need to sync contactcalldetail.', 16, 1)
RETURN -100
END
I see what you are doing, but that will not cause the job to fail. It will simply complete. If you want the agent job to fail, you have to force it.
Jared
CE - Microsoft
January 11, 2012 at 12:58 pm
If you know the code for this, then, please by all means put it in the thread
Oh and BTW this code does work
January 11, 2012 at 1:01 pm
DeanORenO (1/11/2012)
If you know the code for this, then, please by all means put it in the threadOh and BTW this code does work
Interesting... So this:
IF @@ROWCOUNT = 0
BEGIN
RAISERROR('You need to sync contactcalldetail.', 16, 1)
RETURN -100
END
When your rowcount is 0, the job actually fails?
Jared
CE - Microsoft
January 11, 2012 at 1:06 pm
Please review the specs on RAISEERROR
January 11, 2012 at 1:23 pm
DeanORenO (1/11/2012)
Please review the specs on RAISEERROR
I think I was misunderstanding you. Now that you have this, what is the goal? Why would you want a job to fail just for an alert. Job should complete, but alert you of the condition. A job not completing should be an indication of a problem with the job itself, not a condition of the data.
Jared
CE - Microsoft
January 11, 2012 at 1:37 pm
The job failure triggers an email sent to an operator which is done
January 11, 2012 at 1:59 pm
DeanORenO (1/11/2012)
The job failure triggers an email sent to an operator which is done
I don't see the reason for this. You want your job to complete, but send an email. You should be using something like sp_send_dbmail instead of letting the job fail. A job failure should indicate a problem with the job itself or the code that a step is running. Not a problem with the data. Of course this is an opinion, but it is counter-intuitive to have a job fail when the job is doing what it is supposed to do.
Am I making sense here? i.e. If I have a job check for errors in the log, I don't want the job to fail and alert me that there are errors. I want the job to complete and report those errors to me. If the job fails, I don't assume there is a problem with the data, I assume there is a problem with the job and I look there. In this case I would think that the job was in contention with another job or that something was configured wrong with the job itself.
What if there IS something wrong with the job itself, and now you go looking at your data or the script and can't figure out the issue. This is why I think it is a better practice to separate the 2.
Jared
CE - Microsoft
January 11, 2012 at 3:03 pm
I didn't explain what the whole job is about and I don't need to since the raiserror solved my problem, if the query returns rows then the job is not to send mail or do any alerts, however, if the query returns no rows then the alarm is sounded. I don't make up the requirements I just make things work based on what I'm told.
You can keep making up scenarios if you want but I'm done and you didn't help.
January 11, 2012 at 3:44 pm
DeanORenO (1/11/2012)
I didn't explain what the whole job is about and I don't need to since the raiserror solved my problem, if the query returns rows then the job is not to send mail or do any alerts, however, if the query returns no rows then the alarm is sounded. I don't make up the requirements I just make things work based on what I'm told.You can keep making up scenarios if you want but I'm done and you didn't help.
WOW! The point of a forum is not just to help the OP, but to make sure that other users have the tools they need to do their jobs correctly. You are doing yours wrong, and I'm sorry that your job is so disappointing that you have to be rude. For those of you out there reading this, please don't create an error for the job to send an alert. The step should send the alert on its own and run to completion letting you know that the agent job is configured correctly.
Jared
CE - Microsoft
January 12, 2012 at 12:18 pm
The problem with oddball solutions like this one, using a tool in a way it's not intended/documented for, is that you then have to overdocument it yourself to explain, in the job, what the error is intended to accomplish.
Otherwise, a year from now, an error will come through, and nobody will remember why that error command is in there.
A better solution would have been something that uses the Exist function to test for the existence of the desired row, and sp_send_dbmail to send the alert, possibly combined with an abort command for the job itself.
Those steps would be self-documenting to a very large degree. It would still be good, in the Exist check, to document "why" you're checking that data, but you don't have to document anywhere near so heavily, because the alert (and abort, if appropriate) will be obvious in terms of function and methodology.
So, yes, Raiserror will work here, but it makes more work later, for you or for someone else.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 3, 2012 at 3:24 pm
I think both are correct.
To solve the problem, all you need to do is use raiseerror in step 1 within the sql statement and then use step 2 to send an email to the operator .. just set step 1 to go to step 2 on failure and then step 2 completes and job completes.
If you need the job to fail and use the alert system to send a notification to an operator then use the alerts while creating the job to notifiy the operator.
Either way, it is the way you would want to configure the job to send the notification .. So its your choice of setup.
Simple enough?
August 3, 2012 at 4:16 pm
I will chime in with support for do not use job failures to indicate a procedure or data problem. A job failure should be investigated by a DBA to determine what is wrong with the job. A test query should notify the interested party with the results of the test. It is not a matter of preference as much as using the right tool for the right task. Just because you can do something doesn't mean you should.
The rudeness in the response is truly astounding. We are all trying to help and educate each other. The questions and comments were spot on for the situation. Karma will undoubtedly destroy the posters hard drive very soon. Good luck.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply