logging error messages in table problemwith@@error

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

logging error messages in table problemwith@@error

Author

Topic

giovi2002
Starting Member

46 Posts

Posted - 2005-06-12 : 06:12:01

I've created a table to log error codes named error_storage. The Vw_sysmessages links to the sysmessages table in the master database. Checked out, works fine.

The query of my stored procedure results in three errors because of constraints in my table (27 records ok, 3 errors). If I replace @@error in the where statement in for instance 502 the procedure will log three errors in error_storage with the 502 description. When using the internal @@error as a parameter nothing will be appended to my erro table...do you know how to use the @@error value as a variable in the where clause? (i already tried to pass the value to another parameter, but that still resulted in no append action)

Within a specific part of my stored procedure it's the following code
IF @@Error<>0
begin
Insert into Error_Storage(Error, origin)
(Select description, 'Import' As origin
from Vw_sysmessages
Where error= @@Error)

And this one beneath will log all my errors but with one static description because of the 502 clause

IF @@Error<>0
begin
Insert into Error_Storage(Error, origin)
(Select description, 'Import' As origin
from Vw_sysmessages
Where error= 502)

Can you help me? When I use print @@error in the statement it results in 0

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-06-12 : 08:22:36

@@ERROR gets reset after each statement. You need to save the value of @@ERROR in a local variable.

SELECT 1/0
IF @@ERROR<>0
	SELECT @@ERROR

DECLARE @err INT
SELECT 1/0
SET @err = @@ERROR
IF @err<>0
	SELECT @err

rockmoose

giovi2002
Starting Member

46 Posts

Posted - 2005-06-12 : 13:54:37

quote:
Originally posted by rockmoose

@@ERROR gets reset after each statement. You need to save the value of @@ERROR in a local variable.
SELECT 1/0
IF @@ERROR<>0
SELECT @@ERROR

DECLARE @err INT
SELECT 1/0
SET @err = @@ERROR
IF @err<>0
SELECT @err

rockmoose

Thanks rockmoose tomorrow i'll give it a try.
Can you tell me why you need the select 1/0 in the statement?

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-06-12 : 15:33:08

It was just to raise an error as example.

rockmoose

giovi2002
Starting Member

46 Posts

Posted - 2005-06-14 : 09:15:43

quote:
Originally posted by rockmoose

It was just to raise an error as example.

rockmoose

Too bad, it didn't work in my procedure.
The part logging the error is the following:
IF @@Error<>0
begin
SET @err = @@ERROR
SELECT @err
--error storage is my table to log errors
Insert into Error_Storage(Error, origin)
(Select description, 'Import_punctualiteiten' As origin
from Vw_sysmessages
Where error= @err)

giovi2002
Starting Member

46 Posts

Posted - 2005-06-14 : 09:21:34

I performed the error trapping in query analyzer and it works

Declare @err As integer
Set @err=20500
Insert into Error_Storage(Error, origin)
(Select description, 'Import_punctualiteiten' As origin
from Vw_sysmessages
Where error= @err)

I tested the first part IF @@ERROR<>0 Begin etc.
by making @err a static datatype with value 502, that worked.

So the passing of @@error to the variable still doesn't work

giovi2002
Starting Member

46 Posts

Posted - 2005-06-14 : 09:39:34

NOW IT'S WORKING!

My @err resetted the @@error value,
so this is the correct code, i've replaced @@error with @err

Select @err = @@ERROR
IF @err<>0
begin

Insert into Error_Storage(Error, origin)
(Select description, 'Import_punctualiteiten' As origin
from Vw_sysmessages
Where error= @err)

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-06-14 : 10:43:09

Yes You have to catch it right after the statement. (same with @@ROWCOUNT btw)

declare @err int, @rwcnt int

<some sql statement>
select @err = @@ERROR, @rwcnt = @@ROWCOUNT -- catch them right after the statement !

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources