SQL errors, trapping in Server Agent

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

Matik (marzec@sauron.xo.pl)  writes:
> 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.

When you view job history, there is a check box to include the step
details. This is usually where the error message is hiding.

You can also define an output file for the step.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Thank you Erland,

As usual great reply.

Thanks.

Mateusz