Posted by Mike Epprecht \(SQL MVP\) on 05/25/05 23:04

Hi

You need to check for the value of @@error after every statement in a batch
or SP, not just once at the end.

There is a problem with your error handler. @@error gets reset after every
statement, so your IF @@Error <> 0 is in effect setting it back to 0.

You need to change it to the following:

SET @LocalErrorVar = @@Error
IF @LocalErrorVar <> 0
BEGIN
EXEC dbo.HandleError
@ErrorNum = @LocalErrorVar,
@ProcID = @@PROCID
END


--
--------------------------------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

IM: mike@epprecht.net

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

"Steve" <fizzy1236@yahoo.com> wrote in message
news:1117050457.634698.59180@g44g2000cwa.googlegroups.com...
>I have a .NET application with a sql server database backend. Our
> client has been having problems lately with data being returned from
> the database with wrong data. We have error logging in our stored
> procedures and even this is reporting wrong. In every stored procedure
> we have the following:
>
> IF @@Error <> 0
> BEGIN
> EXEC dbo.HandleError
> @ErrorNum = @@Error,
> @ProcID = @@PROCID
> END
>
> Then, HandleError looks like:
>
> CREATE PROCEDURE dbo.HandleError
> @ErrorNum int,
> @ProcID int
> AS
> /*******************************************************************
> * PROCEDURE: HandleError
> * PURPOSE: Handles logging an error
> * CALLED FM: Other stored procedures
> ********************************************************************/
>
> SET NOCOUNT ON
>
> DECLARE @UserID int
> DECLARE @Host varchar(50)
> DECLARE @Len int
> DECLARE @SPName VARCHAR(100)
> DECLARE @ErrorMsg varchar(500)
> DECLARE @ErrorTrace varchar(4000)
>
> -- Get our host name
> SET @Host = HOST_NAME()
> SET @Len = LEN(@Host) - CHARINDEX(':', @Host)
>
> IF @Len > 0 AND CHARINDEX(':', @Host) > 0
> SET @UserID = RIGHT(@Host, LEN(@Host) - CHARINDEX(':', @Host))
> ELSE
> SET @UserID = NULL
>
> SET @SPName = OBJECT_NAME(@ProcID)
>
> SET @ErrorMsg = 'SQL Error: ' + @SPName
>
> SET @ErrorTrace = 'Error: '
> + CAST(@ErrorNum AS VARCHAR(50))
>
> EXEC dbo.InsertApplicationErrors
> @ErrorMessage = @ErrorMsg,
> @ExceptionMessage = '',
> @ErrorStackTrace = @ErrorTrace,
> @UserID = @UserID,
> @HostID = @Host,
> @Logfile = '';
>
> SET NOCOUNT OFF
>
> GO
>
> InsertApplicationErrors inserts a log into a table we have that we
> monitor. We got an error back in the ErrorStackTrace column with
> 'Error: 0'. That shouldn't happen, as the only time we log error is if
> it is not 0.
>
> Does anyone have any ideas on what might be wrong?
>
> Steve
>

[Back to original message]