RAISERROR and Job History

[pandatime]

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:

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

 

[pandatime]

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