|
|
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
>
Navigation:
[Reply to this message]
|