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(@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

[Mike Epprecht \(SQL MVP\)]

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: mi**@epprecht.net

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

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

"Steve" <fi*******@yahoo.com> wrote in message
news:11*********************@g44g2000cwa.googlegro ups.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

[Erland Sommarskog]

Steve (fi*******@yahoo.com) writes:

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

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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[MGFoster]

Steve wrote:

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

-----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:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQpTckYechKqOuFEgEQJWHwCfX7UXm/Z+2G45a29oZHy9fA1Z1i4AnAiB
G+K2wPgJC4EKqYIZsgI4x5rQ
=G9He
-----END PGP SIGNATURE-----

[Steve]

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]

Steve (fi*******@yahoo.com) writes:

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.

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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Erland Sommarskog]

Erland Sommarskog (es****@sommarskog.se) writes:

Steve (fi*******@yahoo.com) writes:

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.

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.

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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Steve]

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?