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

.

Relevant Pages

  • Re: Full text catalog just not populating
    ... exec sp_defaultdb N'NT Authority\System', N'master' ... means either the network guys in my company who don't know SQL but are admins ... > needs this login to log into SQL Server and you can either add back this ... >> fetching U ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Indexing delay for one row.
    ... script and WAITFOR and varying the delay from 1 to 18 seconds. ... could you confirm your exact version of SQL Server that you are seeing ... It is possible that a change was made to the pooling frequence under SP3 to ... exec sp_fulltext_table 'FTSTable','create','FTSCatalog','FTSTable_IDX' ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Casting to VARCHAR(MAX)
    ... But when you do the EXEC, the statement the EXEC passes to SQL Server looks like ... So the string that gets put into #TEMP1 will be truncated to 8000 characters. ... that will force the whole expression concatenating the strings to return VARCHARand you would get the whole string inserted into the table. ...
    (microsoft.public.sqlserver.programming)
  • Re: storing and searching office docs in SQL
    ... You CAN both store and search the contents of the MS Word ... files stored in an SQL Table's FT-enable IMAGE column, ... FTS CONTAINS or FREETEXT to search the contents of that MS word document: ... exec sp_fulltext_database 'enable' -- only do this once! ...
    (microsoft.public.sqlserver.programming)
  • Re: SqlDataAdapter1.SelectCommand.CommandType= CommandType.StoredProcedure
    ... > kann man beim EXEC PROC keine Parameter beifügen. ... CommandType.StoredProcedure wird intern als RPC Command abgesetzt, ... SQL RPC siehe SQL Server Dokumentation, ... nach der Ausführung ungültig werden. ...
    (microsoft.public.de.german.entwickler.dotnet.datenbank)
Loading