none
Error handler in SQL

Respostas

  • Hi,

    There is a pretty good example in the AdventureWorks2008 sample database.

    These obejcts are required for the sample:

    
    CREATE TABLE [dbo].[ErrorLog](
    	[ErrorLogID] [int] IDENTITY(1,1) NOT NULL,
    	[ErrorTime] [datetime] NOT NULL,
    	[UserName] [sysname] NOT NULL,
    	[ErrorNumber] [int] NOT NULL,
    	[ErrorSeverity] [int] NULL,
    	[ErrorState] [int] NULL,
    	[ErrorProcedure] [nvarchar](126) NULL,
    	[ErrorLine] [int] NULL,
    	[ErrorMessage] [nvarchar](4000) NOT NULL,
     CONSTRAINT [PK_ErrorLog_ErrorLogID] PRIMARY KEY CLUSTERED 
    (
    	[ErrorLogID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    
    
    
    -- uspLogError logs error information in the ErrorLog table about the 
    -- error that caused execution to jump to the CATCH block of a 
    -- TRY...CATCH construct. This should be executed from within the scope 
    -- of a CATCH block otherwise it will return without inserting error 
    -- information. 
    CREATE PROCEDURE [dbo].[uspLogError] 
      @ErrorLogID [int] = 0 OUTPUT -- contains the ErrorLogID of the row inserted
    AS                -- by uspLogError in the ErrorLog table
    BEGIN
      SET NOCOUNT ON;
    
      -- Output parameter value of 0 indicates that error 
      -- information was not logged
      SET @ErrorLogID = 0;
    
      BEGIN TRY
        -- Return if there is no error information to log
        IF ERROR_NUMBER() IS NULL
          RETURN;
    
        -- Return if inside an uncommittable transaction.
        -- Data insertion/modification is not allowed when 
        -- a transaction is in an uncommittable state.
        IF XACT_STATE() = -1
        BEGIN
          PRINT 'Cannot log error since the current transaction is in an uncommittable state. ' 
            + 'Rollback the transaction before executing uspLogError in order to successfully log error information.';
          RETURN;
        END
    
        INSERT [dbo].[ErrorLog] 
          (
          [UserName], 
          [ErrorNumber], 
          [ErrorSeverity], 
          [ErrorState], 
          [ErrorProcedure], 
          [ErrorLine], 
          [ErrorMessage]
          ) 
        VALUES 
          (
          CONVERT(sysname, CURRENT_USER), 
          ERROR_NUMBER(),
          ERROR_SEVERITY(),
          ERROR_STATE(),
          ERROR_PROCEDURE(),
          ERROR_LINE(),
          ERROR_MESSAGE()
          );
    
        -- Pass back the ErrorLogID of the row inserted
        SET @ErrorLogID = SCOPE_IDENTITY();
      END TRY
      BEGIN CATCH
        PRINT 'An error occurred in stored procedure uspLogError: ';
        RETURN -1;
      END CATCH
    END;
    
    

    Here is a sample sproc using error hanlder in a TRY/CATCH block:

    CREATE PROCEDURE [HumanResources].[uspUpdateEmployeeLogin]
      @BusinessEntityID [int], 
      @OrganizationNode [hierarchyid],
      @LoginID [nvarchar](256),
      @JobTitle [nvarchar](50),
      @HireDate [datetime],
      @CurrentFlag [dbo].[Flag]
    WITH EXECUTE AS CALLER
    AS
    BEGIN
      SET NOCOUNT ON;
    
      BEGIN TRY
        UPDATE [HumanResources].[Employee] 
        SET [OrganizationNode] = @OrganizationNode 
          ,[LoginID] = @LoginID 
          ,[JobTitle] = @JobTitle 
          ,[HireDate] = @HireDate 
          ,[CurrentFlag] = @CurrentFlag 
        WHERE [BusinessEntityID] = @BusinessEntityID;
      END TRY
      BEGIN CATCH
        EXECUTE [dbo].[uspLogError];
      END CATCH;
    END;
    

    I hope it helps.

    J.


    There are 10 type of people. Those who understand binary and those who do not.
    segunda-feira, 23 de maio de 2011 10:56
  • Hello Ravi,

    Please check below links

     

    http://weblogs.asp.net/alex_papadimoulis/archive/2005/02/02/365685.aspx

    http://www.sqlservercentral.com/articles/news/exceptionhandlinginsqlserver2005/2237/

    http://www.sommarskog.se/error-handling-I.html

    http://www.sommarskog.se/error-handling-II.html

     

    Thanks,


    Raheel Khan
    segunda-feira, 30 de maio de 2011 12:00

Todas as Respostas

  • Hi,

    There is a pretty good example in the AdventureWorks2008 sample database.

    These obejcts are required for the sample:

    
    CREATE TABLE [dbo].[ErrorLog](
    	[ErrorLogID] [int] IDENTITY(1,1) NOT NULL,
    	[ErrorTime] [datetime] NOT NULL,
    	[UserName] [sysname] NOT NULL,
    	[ErrorNumber] [int] NOT NULL,
    	[ErrorSeverity] [int] NULL,
    	[ErrorState] [int] NULL,
    	[ErrorProcedure] [nvarchar](126) NULL,
    	[ErrorLine] [int] NULL,
    	[ErrorMessage] [nvarchar](4000) NOT NULL,
     CONSTRAINT [PK_ErrorLog_ErrorLogID] PRIMARY KEY CLUSTERED 
    (
    	[ErrorLogID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    
    
    
    -- uspLogError logs error information in the ErrorLog table about the 
    -- error that caused execution to jump to the CATCH block of a 
    -- TRY...CATCH construct. This should be executed from within the scope 
    -- of a CATCH block otherwise it will return without inserting error 
    -- information. 
    CREATE PROCEDURE [dbo].[uspLogError] 
      @ErrorLogID [int] = 0 OUTPUT -- contains the ErrorLogID of the row inserted
    AS                -- by uspLogError in the ErrorLog table
    BEGIN
      SET NOCOUNT ON;
    
      -- Output parameter value of 0 indicates that error 
      -- information was not logged
      SET @ErrorLogID = 0;
    
      BEGIN TRY
        -- Return if there is no error information to log
        IF ERROR_NUMBER() IS NULL
          RETURN;
    
        -- Return if inside an uncommittable transaction.
        -- Data insertion/modification is not allowed when 
        -- a transaction is in an uncommittable state.
        IF XACT_STATE() = -1
        BEGIN
          PRINT 'Cannot log error since the current transaction is in an uncommittable state. ' 
            + 'Rollback the transaction before executing uspLogError in order to successfully log error information.';
          RETURN;
        END
    
        INSERT [dbo].[ErrorLog] 
          (
          [UserName], 
          [ErrorNumber], 
          [ErrorSeverity], 
          [ErrorState], 
          [ErrorProcedure], 
          [ErrorLine], 
          [ErrorMessage]
          ) 
        VALUES 
          (
          CONVERT(sysname, CURRENT_USER), 
          ERROR_NUMBER(),
          ERROR_SEVERITY(),
          ERROR_STATE(),
          ERROR_PROCEDURE(),
          ERROR_LINE(),
          ERROR_MESSAGE()
          );
    
        -- Pass back the ErrorLogID of the row inserted
        SET @ErrorLogID = SCOPE_IDENTITY();
      END TRY
      BEGIN CATCH
        PRINT 'An error occurred in stored procedure uspLogError: ';
        RETURN -1;
      END CATCH
    END;
    
    

    Here is a sample sproc using error hanlder in a TRY/CATCH block:

    CREATE PROCEDURE [HumanResources].[uspUpdateEmployeeLogin]
      @BusinessEntityID [int], 
      @OrganizationNode [hierarchyid],
      @LoginID [nvarchar](256),
      @JobTitle [nvarchar](50),
      @HireDate [datetime],
      @CurrentFlag [dbo].[Flag]
    WITH EXECUTE AS CALLER
    AS
    BEGIN
      SET NOCOUNT ON;
    
      BEGIN TRY
        UPDATE [HumanResources].[Employee] 
        SET [OrganizationNode] = @OrganizationNode 
          ,[LoginID] = @LoginID 
          ,[JobTitle] = @JobTitle 
          ,[HireDate] = @HireDate 
          ,[CurrentFlag] = @CurrentFlag 
        WHERE [BusinessEntityID] = @BusinessEntityID;
      END TRY
      BEGIN CATCH
        EXECUTE [dbo].[uspLogError];
      END CATCH;
    END;
    

    I hope it helps.

    J.


    There are 10 type of people. Those who understand binary and those who do not.
    segunda-feira, 23 de maio de 2011 10:56
  • Hello Ravi,

    Please check below links

     

    http://weblogs.asp.net/alex_papadimoulis/archive/2005/02/02/365685.aspx

    http://www.sqlservercentral.com/articles/news/exceptionhandlinginsqlserver2005/2237/

    http://www.sommarskog.se/error-handling-I.html

    http://www.sommarskog.se/error-handling-II.html

     

    Thanks,


    Raheel Khan
    segunda-feira, 30 de maio de 2011 12:00