Posted by Matik on 11/28/05 22:10

Hello to all,

I've fallow problem. I've a sp called as a job of SA each minute. This
runs pretty nice, but from time to time, the job is aborted, and I
don't know why.

Considering my logging, which is implemented in DB, I know, in which
point it is happening, but I don't know the exact error.
This one is for sure any SQL server exception.

I wanted to track this error, but reading all news, and help, and
performing some tests, I've find out, that this is almost like
impossible, to catch the error in t-sql code (for example in this sp),
and wirte it to any table for futher review.
Reading great documentation from Erland Sommarskog, I know, there is
no way to catch this error in t-sql, because, usualy the sql terminates
execution of the code immieadetly (so I found it also by my tests).

Now, my question is: sice I'm calling this sp continously in Server
Agent as a job scheduled to be called each one minute, is it any way,
to trap this error on this level? In SA? and THEN save it somewhere in
my db?

I'm calling the sp as a 'command' in job step as 'exec
sp_name_of_procedure'.
If I'll try like this:

declare @err int
set @err = 0

exec sp_name_of_procedure
set @err = @@error
if @err <> 0
begin
insert into tbl_logger (sql_error, msg) values (@err, 'SQL raised an
error')
end

will it work, or the sql will assume the whole code as a one batch, and
will terminate after call of sp?

Thank you in advance for reply.

Greatings

Mateusz

[Back to original message]