How to register a stored procedure error

Question

Scenario: C # applications use SQL2000. It excludes 3 stored procs in try catch in the application. In the catch, the error is suppressed. Due to some legitimacy, C # code cannot be modified and implemented.

Q: How to catch the actual SQL error in a saved proc in a log file or other table? @@ Error returns an error message to the application, but when evaluated in the query analyzer, it is always 0, although "If @@ Error <> 0" is triggered. I am trying to keep the error number @@, but always 0.

Please, help.

Answer 1

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

 IF @@ERROR <> 0 ... 

is an operator, and this statement completes successfully, with the result that @@ERROR set to 0.

The correct way to check and use @@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 2

You don't have an example to look at it, but look at using

 RAISERROR ... WITH LOG 

see http://msdn.microsoft.com/en-us/library/aa238452(SQL.80).aspx to learn more about 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 3

I haven’t tried it myself, but I think you can track errors using the Sql Server Profiler .

Answer 4

 ALTER PROCEDURE [dbo].[StoreProcedureName] ( parameters ) AS BEGIN SET NOCOUNT On BEGIN TRY --type your query End Try BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity, ERROR_STATE() AS ErrorState, ERROR_PROCEDURE() AS ErrorProcedure, ERROR_LINE() AS ErrorLine, ERROR_MESSAGE() AS ErrorMessage RETURN -1 END CATCH End