As a SQL Server DBA, it is crucial to have a robust error handling mechanism in place. SQL Server 2005 introduced a new error handler that allows for more elegant and limitless error handling compared to its predecessor, SQL Server 2000.

In this article, we will explore the importance of error handling and how to implement a standard error handler mechanism in SQL Server 2005.

Why is Error Handling Important?

Regardless of the complexity of a process, it is essential to anticipate and handle any errors that may occur. Error handling allows us to write exceptions, notify users, and identify the cause of the error. By incorporating a logging mechanism, we can save error data for proper debugging.

Implementing the Error Handling Mechanism

To implement the error handling mechanism, we first need to create an error log table to store error and system data. Here is an example of the SQL code to create the error log table:

CREATE TABLE ERROR_LOG (
  [ERROR_LOG_ID] [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY,
  [ERROR_LOG_PROGRAM_NM] [varchar] (128) NULL,
  [ERROR_LINE_NO] INT NULL,
  [ERROR_LOG_ERROR_NO] [int] NULL,
  [ERROR_LOG_ERROR_DSC] [varchar] (4000) NULL,
  [ERROR_SEVERITY_NO] INT NULL,
  [ERROR_STATE_NO] INT NULL,
  [ERROR_LOG_PROGRAM_SECTION_NM] [varchar] (255) NULL,
  [ERROR_LOG_SPID_NO] [int] NULL,
  [ERROR_LOG_EVENT] [varchar] (255) NULL,
  [ERROR_LOG_PARAMETER] [int] NULL,
  [ERROR_LOG_EVENT_INFO] [varchar] (1000) NULL,
  [ERROR_DB_NAME] [varchar] (50) NULL,
  [CREATEDATE] [DATETIME] NOT NULL,
  [CREATEUSERNAME] [VARCHAR] (128) NOT NULL,
  [CREATEMACHINENAME] [VARCHAR] (128) NULL,
  [CREATESOURCE] [VARCHAR] (128) NULL
)

Once the error log table is created, we can proceed to create a stored procedure that will load error-tracked information into the table. This stored procedure takes information provided by the code and stores it in the error log table. It also utilizes the DBCC INPUTBUFFER command to capture the last statement sent from a client to SQL Server.

Here is an example of the SQL code to create the error logging stored procedure:

CREATE PROCEDURE ERROR_LOG_2005
    @ERROR_LOG_PROGRAM_NM VARCHAR(128) = NULL,
    @ERROR_LOG_PROGRAM_SECTION_NM VARCHAR(255) = NULL,
    @ERROR_LOG_ERROR_NO INT = NULL,
    @ERROR_LOG_ERROR_DSC VARCHAR(4000) = NULL,
    @ERROR_DB_NAME VARCHAR(50) = NULL
AS
BEGIN
    -- Implementation details of the stored procedure
END

Additionally, we can create a template stored procedure that serves as a standardized procedure for the entire company. This template can handle both non-transactional and transactional procedures, ensuring that incomplete transactions are never committed and uncommitted or non-rollback transactions are not left behind.

Here is an example of the SQL code to create the template stored procedure:

CREATE PROCEDURE TEMPLATE_2005
AS
BEGIN
    -- Implementation details of the template stored procedure
END

Conclusion

Implementing a robust error handling mechanism is crucial for SQL Server DBAs. SQL Server 2005 provides a new error handler that allows for more elegant and limitless error handling compared to its predecessor. By incorporating error logging and utilizing standardized stored procedure templates, DBAs can ensure that errors are properly handled and logged for debugging purposes.

These error handling mechanisms have been successfully used in both development and production environments, serving as a standard for all SQL Server developers.