Sql Server incorrectly functioning

[Steve]

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(@Pr ocID)

SET @ErrorMsg = 'SQL Error: ' + @SPName

SET @ErrorTrace = 'Error: '
+ CAST(@ErrorNum AS VARCHAR(50))

EXEC dbo.InsertAppli cationErrors
@ErrorMessage = @ErrorMsg,
@ExceptionMessa ge = '',
@ErrorStackTrac e = @ErrorTrace,
@UserID = @UserID,
@HostID = @Host,
@Logfile = '';

SET NOCOUNT OFF

GO

InsertApplicati onErrors 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

[Mike Epprecht \(SQL MVP\)]

Re: Sql Server incorrectly functioning

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.n et

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

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

"Steve" <fizzy1236@yaho o.com> wrote in message
news:1117050457 .634698.59180@g 44g2000cwa.goog legroups.com...[color=blue]
>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(@Pr ocID)
>
> SET @ErrorMsg = 'SQL Error: ' + @SPName
>
> SET @ErrorTrace = 'Error: '
> + CAST(@ErrorNum AS VARCHAR(50))
>
> EXEC dbo.InsertAppli cationErrors
> @ErrorMessage = @ErrorMsg,
> @ExceptionMessa ge = '',
> @ErrorStackTrac e = @ErrorTrace,
> @UserID = @UserID,
> @HostID = @Host,
> @Logfile = '';
>
> SET NOCOUNT OFF
>
> GO
>
> InsertApplicati onErrors 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
>[/color]

[Erland Sommarskog]

Re: Sql Server incorrectly functioning

Steve (fizzy1236@yaho o.com) writes:[color=blue]
> 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[/color]

I've bad news for you: you have lots of stored procedures to edit.
@@error is set after each statement, and that includes IF. So by the
time execution reaches the EXEC statement @@error is 0.

I have an article on my web site about error handling in stored procedures
that you may find useful, http://www.sommarskog.se/error-handling-II.html.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[MGFoster]

Re: Sql Server incorrectly functioning

Steve wrote:[color=blue]
> 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(@Pr ocID)
>
> SET @ErrorMsg = 'SQL Error: ' + @SPName
>
> SET @ErrorTrace = 'Error: '
> + CAST(@ErrorNum AS VARCHAR(50))
>
> EXEC dbo.InsertAppli cationErrors
> @ErrorMessage = @ErrorMsg,
> @ExceptionMessa ge = '',
> @ErrorStackTrac e = @ErrorTrace,
> @UserID = @UserID,
> @HostID = @Host,
> @Logfile = '';
>
> SET NOCOUNT OFF
>
> GO
>
> InsertApplicati onErrors 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
>[/color]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You have to set a variable to hold the @@error value, 'cuz doing this:

IF @@error <> 0

causes the @@error value to reset to zero (0). E.g.:

If @@error <> 0
print @@error

will return 0 for the "print @@error" statement.

So, do it this way:

DECLARE @err INT
SET @err = @@error
IF @err <> 0
print @err

will return the error number for the "print @err" statement.
--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQpTckYechKq OuFEgEQJWHwCfX7 UXm/Z+2G45a29oZHy9f A1Z1i4AnAiB
G+K2wPgJC4EKqYI ZsgI4x5rQ
=G9He
-----END PGP SIGNATURE-----

[Steve]

Re: Sql Server incorrectly functioning

Thanks, I will give a good read through and rework this. Not too happy
with Microsoft's documentation, as they state I should be able to do
this. From Sql Server Books Online...

-- Save any non-zero @@ERROR value.
IF (@@ERROR <> 0)
SET @ErrorSave = @@ERROR

Found in the using @@ERROR section.

UGH! You aren't kidding, we have over 1000 SPs I get to re-edit and
change where we are check for the errors.

[Erland Sommarskog]

Re: Sql Server incorrectly functioning

Steve (fizzy1236@yaho o.com) writes:[color=blue]
> Thanks, I will give a good read through and rework this. Not too happy
> with Microsoft's documentation, as they state I should be able to do
> this. From Sql Server Books Online...
>
> -- Save any non-zero @@ERROR value.
> IF (@@ERROR <> 0)
> SET @ErrorSave = @@ERROR
>
> Found in the using @@ERROR section.[/color]

See below for a link to an updated version of Books Online. It is indeed
an embarrssing error. But the same section also says:

Because @@ERROR is cleared and reset on each statement executed, check
it immediately following the statement validated, or save it to a local
variable that can be checked later.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Erland Sommarskog]

Re: Sql Server incorrectly functioning

Erland Sommarskog (esquel@sommars kog.se) writes:[color=blue]
> Steve (fizzy1236@yaho o.com) writes:[color=green]
>> Thanks, I will give a good read through and rework this. Not too happy
>> with Microsoft's documentation, as they state I should be able to do
>> this. From Sql Server Books Online...
>>
>> -- Save any non-zero @@ERROR value.
>> IF (@@ERROR <> 0)
>> SET @ErrorSave = @@ERROR
>>
>> Found in the using @@ERROR section.[/color]
>
> See below for a link to an updated version of Books Online. It is indeed
> an embarrssing error. But the same section also says:
>
> Because @@ERROR is cleared and reset on each statement executed, check
> it immediately following the statement validated, or save it to a local
> variable that can be checked later.[/color]

By the way, while I don't want to embarrass you, I would like to point
out that this mishap could have been avoided if you had tested the
error handling, before you spread it all over town. Error-handling code
is one that is often over-looked when testing, but good testing should
also cover some error cases to see that they are handled well.

On a completely other note: SQL 2005 gives you exception handling with
TRY-CATCH syntax, which makes error handling in SQL 2005 a lot more
pleasant.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Steve]

Re: Sql Server incorrectly functioning

Well, in theory I did test the error handling. I suppose I didn't go
an execute code that would cause an error, I simply set our error we
wanted logged. I suppose in hind sight, testing with causing an error
would have been better than just saying our error is 1. But, then
again, who expects the documentation to be wrong?