Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

RAISERROR and Job History

Status
Not open for further replies.

pandatime

Programmer
Joined
Jan 29, 2010
Messages
92
Location
AU
Hi,

I'm noticing some interesting behavior on the part of sql server.

This is my code, which is in a stored proc, which gets called from a test job:

Code:
RAISERROR('ANOTHER ONE', 16, 2)
insert into test values (111)
RAISERROR('ANOTHER TWO', 16, 2) -- doesn't appear
insert into test values (222)

When I look at the job history after running it, I only see the first raiserror message, 'ANOTHER ONE', but not the second raiserror message.

However, both inserts to the 'test' table do occur, so obviously the code keeps running after the first raiserror. In other words, not send back execution to the job until all the code is finished, but also doesn't log the second raiserror.

Is there a way to change this behavior??? I really want to be able to log both messages, separately.

Thanks
 
Ok I am learning a lot here but it doesn't entirely make sense.

If I change the severity level to 10 instead of 16, it DOES log both messages, since level 10 is informational only. And it does not fail the job.

So I guess I need to use a mix of severity levels to achieve what I want.

And btw, level 16 does not stop the proc from executing. You still need to add a "return" after it, if you want to terminate the proc immediately.

Does anyone else's experience confirm this? I'm reading conflicting reports.

Thanks
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top