如何记录存储过程错误

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.

场景:C#apps使用SQL2000。它在应用程序的try catch中排除了3个存储过程。在捕获中,错误被抑制。由于某些合法性,c#代码无法更改和实现。

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.

问:如何将存储过程中的实际SQL错误捕获到日志文件或其他表中? @@ Error向应用程序返回错误消息,但在查询分析器中进行评估时,它始终为0,尽管'If @@ Error <> 0'会触发。我尝试存储@@错误号,但它始终为0。

Please help.

4 个解决方案

#1

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

检查时@@ ERROR重置为0。为什么?因为它反映了最后执行的语句的状态。

IF @@ERROR <> 0 ...

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

是一个语句,该语句成功,导致@@ ERROR设置为0。

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

检查和操作@@ ERROR值的正确方法如下:

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

#2

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.

请参阅:http://msdn.microsoft.com/en-us/library/aa238452(SQL.80).aspx了解更多相关信息。

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

写入事件日志。有关详细信息,请访问http://msdn.microsoft.com/en-us/library/aa260695(SQL.80).aspx

#3

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

没有自己尝试,但我猜你可以使用Sql Server Profiler监控错误。

#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

#1