How to log a stored procedure error

Question

Scenario: C# apps uses SQL2000. It excecute 3 stored procs within a try catch in the app. In the catch the error is suppressed. Due to some legalities, the c# code cannot be changed and implemented.

Q: How do I trap the actual SQL error in the stored proc into a log file or other table? @@Error returns an error message to the app but when evaluated in query analyzer it is always 0 although the 'If @@Error <> 0' does fire. I try to store the @@Error number, but it is always 0.

Please help.

Answer 1

Haven't got an example to hand, but look at using

RAISERROR ... WITH LOG

see: http://msdn.microsoft.com/en-us/library/aa238452(SQL.80).aspx for more on this.

Or use:

xp_logevent {error_number, 'message'} [, 'severity']

to write to the event log. More details at http://msdn.microsoft.com/en-us/library/aa260695(SQL.80).aspx

Answer 2

@@ERROR is reset to 0 when you check it. Why? Because it reflects the status of the last statement executed.

IF @@ERROR <> 0 ...

is a statement, and that statement succeeds, causing @@ERROR to be set to 0.

The proper way to examine and operate on @@ERROR values is as follows:

DECLARE @ErrorCode INT

INSERT INTO Table ...

SET @ErrorCode = @@ERROR
IF @ErrorCode <> 0
BEGIN
    INSERT INTO ErrorLog (ErrorCode, Message)
        VALUES (@ErrorCode, 'The INSERT operation failed.')
END

Answer 3

Didn't try it myself but I guess you can monitor the errors with Sql Server Profiler.

Answer 4

Thanks alot for the info on how @@error works!