<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2008
»
SQL Server 2008 Administration
»
Sql agent job step - tsql command
15 posts, Page 1 of 2
1
2
»»
Sql agent job step - tsql command
Rate Topic
Display Mode
Topic Options
Author
Message
DeanORenO
DeanORenO
Posted Wednesday, January 11, 2012 10:49 AM
SSC Rookie
Group: General Forum Members
Last Login: Thursday, March 29, 2012 7:00 AM
Points: 37,
Visits: 133
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?
Post #1234204
SQLKnowItAll
SQLKnowItAll
Posted Wednesday, January 11, 2012 10:51 AM
SSCrazy
Group: General Forum Members
Last Login: Yesterday @ 8:14 AM
Points: 2,614,
Visits: 3,130
I just force it to fail. SELECT * FROM tableThatDoesNotExist
Thanks,
Jared
SQL Know-It-All
How to post data/code on a forum to get the best help - Jeff Moden
Post #1234207
DeanORenO
DeanORenO
Posted Wednesday, January 11, 2012 12:53 PM
SSC Rookie
Group: General Forum Members
Last Login: Thursday, March 29, 2012 7:00 AM
Points: 37,
Visits: 133
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
Post #1234310
SQLKnowItAll
SQLKnowItAll
Posted Wednesday, January 11, 2012 12:55 PM
SSCrazy
Group: General Forum Members
Last Login: Yesterday @ 8:14 AM
Points: 2,614,
Visits: 3,130
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.
Thanks,
Jared
SQL Know-It-All
How to post data/code on a forum to get the best help - Jeff Moden
Post #1234314
DeanORenO
DeanORenO
Posted Wednesday, January 11, 2012 12:58 PM
SSC Rookie
Group: General Forum Members
Last Login: Thursday, March 29, 2012 7:00 AM
Points: 37,
Visits: 133
If you know the code for this, then, please by all means put it in the thread
Oh and BTW this code does work
Post #1234316
SQLKnowItAll
SQLKnowItAll
Posted Wednesday, January 11, 2012 1:01 PM
SSCrazy
Group: General Forum Members
Last Login: Yesterday @ 8:14 AM
Points: 2,614,
Visits: 3,130
DeanORenO (1/11/2012)
If you know the code for this, then, please by all means put it in the thread
Oh 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?
Thanks,
Jared
SQL Know-It-All
How to post data/code on a forum to get the best help - Jeff Moden
Post #1234320
DeanORenO
DeanORenO
Posted Wednesday, January 11, 2012 1:06 PM
SSC Rookie
Group: General Forum Members
Last Login: Thursday, March 29, 2012 7:00 AM
Points: 37,
Visits: 133
Please review the specs on RAISEERROR
http://msdn.microsoft.com/en-us/library/ms178592
Post #1234323
SQLKnowItAll
SQLKnowItAll
Posted Wednesday, January 11, 2012 1:23 PM
SSCrazy
Group: General Forum Members
Last Login: Yesterday @ 8:14 AM
Points: 2,614,
Visits: 3,130
DeanORenO (1/11/2012)
Please review the specs on RAISEERROR
http://msdn.microsoft.com/en-us/library/ms178592
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.
Thanks,
Jared
SQL Know-It-All
How to post data/code on a forum to get the best help - Jeff Moden
Post #1234331
DeanORenO
DeanORenO
Posted Wednesday, January 11, 2012 1:37 PM
SSC Rookie
Group: General Forum Members
Last Login: Thursday, March 29, 2012 7:00 AM
Points: 37,
Visits: 133
The job failure triggers an email sent to an operator which is done
Post #1234342
SQLKnowItAll
SQLKnowItAll
Posted Wednesday, January 11, 2012 1:59 PM
SSCrazy
Group: General Forum Members
Last Login: Yesterday @ 8:14 AM
Points: 2,614,
Visits: 3,130
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.
Thanks,
Jared
SQL Know-It-All
How to post data/code on a forum to get the best help - Jeff Moden
Post #1234359
« Prev Topic
|
Next Topic »
15 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
