[RELEASE] SQL Error Logger

Syloxx · 12/11/2018, 06:16

SQL Error Logger

About:
The purpose of that error logger is to as the name tells log every error caused by an procedure with that logger in mind (probably mainly my releases).

The logger is able to log any system errors and preconfigured user errors (

) if the

is above level 10.

Implementation:
Table:

Code:

USE [SRO_VT_SYSTEM]
GO

/****** Object:  Table [dbo].[_ErrorLogs]    Script Date: 12/11/2018 7:05:03 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[_ErrorLogs](
	[ErrorLogSN] [BIGINT] IDENTITY(1,1) NOT NULL,
	[LoginName] [VARCHAR](128) NOT NULL,
	[HostName] [VARCHAR](128) NOT NULL,
	[ErrorNumber] [INT] NOT NULL,
	[ErrorSeverity] [INT] NULL,
	[ErrorState] [INT] NULL,
	[ErrorProcedure] [VARCHAR](128) NULL,
	[ErrorLine] [INT] NULL,
	[ErrorMessage] [VARCHAR](MAX) NOT NULL,
	[ErrorDate] [DATETIME] NOT NULL,
 CONSTRAINT [PK_ErrorLogs] PRIMARY KEY CLUSTERED 
(
	[ErrorLogSN] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Procedure:

Code:

USE [SRO_VT_SYSTEM]
GO
/****** Object:  StoredProcedure [dbo].[_AddErrorLog]    Script Date: 12/11/2018 7:05:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/**
version : 1
author : Syloxx
created date : 2018-09-20
description : Logs the error that has occurred.
return value :
0 = There is no error that has occurred.
1 = The transaction is in an uncommittable state, rolling back transaction.
100 = There is a system error that has occurred.
**/

ALTER PROCEDURE [dbo].[_AddErrorLog]
    @intProcedureID INT = NULL
AS
SET NOCOUNT ON;

SET XACT_ABORT ON;

DECLARE @vcErrorMessage   VARCHAR(4000)
      , @intErrorNumber   INT
      , @intErrorSeverity INT
      , @intErrorState    INT
      , @intErrorLine     INT
      , @vcErrorProcedure VARCHAR(128);

/**_# If inside an uncommittable transaction, rollback and return 1.*/
IF XACT_STATE() = -1
BEGIN
    GOTO ErrorHandler;
END;

/**_# If there is no error information to log, return 0.*/
IF ERROR_NUMBER() IS NULL
BEGIN
    GOTO ErrorHandler;
END;

/**_## Get required informations of the occurred error.*/
SET @intErrorNumber = ERROR_NUMBER();
SET @intErrorSeverity = ERROR_SEVERITY();
SET @intErrorState = ERROR_STATE();
SET @intErrorLine = ERROR_LINE();
SET @vcErrorProcedure = ERROR_PROCEDURE();
SET @vcErrorMessage = ERROR_MESSAGE();

/**_## Log the error that has occurred.*/
IF @vcErrorProcedure = OBJECT_NAME(@@PROCID)
    INSERT dbo._ErrorLogs
    (
        LoginName
      , HostName
      , ErrorNumber
      , ErrorSeverity
      , ErrorState
      , ErrorProcedure
      , ErrorLine
      , ErrorMessage
      , ErrorDate
    )
    VALUES (CAST(ORIGINAL_LOGIN() AS VARCHAR(128))
          , CAST(HOST_NAME() AS VARCHAR(128))
          , @intErrorNumber
          , @intErrorSeverity
          , @intErrorState
          , ISNULL(OBJECT_NAME(@intProcedureID), @vcErrorProcedure)
          , @intErrorLine
          , @vcErrorMessage
          , GETDATE());
ELSE IF @@NESTLEVEL <= 2
    INSERT dbo._ErrorLogs
    (
        LoginName
      , HostName
      , ErrorNumber
      , ErrorSeverity
      , ErrorState
      , ErrorProcedure
      , ErrorLine
      , ErrorMessage
      , ErrorDate
    )
    VALUES (CAST(ORIGINAL_LOGIN() AS VARCHAR(128))
          , CAST(HOST_NAME() AS VARCHAR(128))
          , @intErrorNumber
          , @intErrorSeverity
          , @intErrorState
          , @vcErrorProcedure
          , @intErrorLine
          , @vcErrorMessage
          , GETDATE());

/**_# Rethrow the error that has occurred.*/
SET @vcErrorMessage = 'Error %d, Level %d, State %d, Procedure %s, Line %d, Message: ' + @vcErrorMessage;

RAISERROR(
             @vcErrorMessage
           , @intErrorSeverity
           , 1
           , @intErrorNumber
           , @intErrorSeverity
           , @intErrorState
           , @vcErrorProcedure
           , @intErrorLine
         );

RETURN 100;

ErrorHandler:
IF XACT_STATE() <> 0
    ROLLBACK TRANSACTION;

RETURN 0;

How to Use:
Here you can find an

.

The most convenient way to use that error logger is by use a

.

Code:

BEGIN TRY
    //YOUR CODE HERE
END TRY
BEGIN CATCH
    EXEC dbo._AddErrorLog;
END CATCH;

Procedure Snippet:

Code:

/**
version : VERSION_NUMBER
author : AUTHOR_NAME
created date : DATETIME
description : PROCEDURE_DESCRIPTION
return value :
0 = Procedure was successfully executed.
1 = The transaction is in an uncommittable state, rolling back transaction.
2 = There is an undefined error that has occurred.
**/

CREATE PROCEDURE [dbo].[PROCEDURE_NAME]
    @PROCEDURE_PARAM INT
AS
SET NOCOUNT ON;
SET XACT_ABORT ON;

DECLARE @intReturnValue INT;

/**_# If inside an uncommittable transaction, rollback and return 1.*/
IF XACT_STATE() = -1
BEGIN
    SET @intReturnValue = 1;
    GOTO ErrorHandler;
END;

BEGIN TRY
    --YOUR CODE GOES HERE
END TRY
BEGIN CATCH
    IF @intReturnValue IS NULL
    OR @intReturnValue = 0
    BEGIN
        SET @intReturnValue = 2;
    END;

    EXEC dbo._AddErrorLog;
    GOTO ErrorHandler;
END CATCH;

RETURN 0;

ErrorHandler:
IF XACT_STATE() <> 0
    ROLLBACK TRANSACTION;

RETURN @intReturnValue;