Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Standardized SQL Server Error Handling and Centralized Logging


By:   |   Read Comments   |   Related Tips: 1 | 2 | 3 | More > Error Handling

Problem
Error handling was not always used in SQL Server 2000 because the techniques were cumbersome. With SQL Server 2005 the TRY and CATCH syntax is available which simplifies the error handling process in all T-SQL code. To take the error handling to the next level, why not standardize the error handling across your T-SQL code and centralize the location of the errors?  Seem like a good idea, right?  So let's jump into how to do this.

Solution
Let's break this down how to setup the error handling into the following steps:

Sample TRY\CATCH code

BEGIN TRY

T-SQL Statement

END TRY

BEGIN CATCH

T-SQL Statement

END CATCH

Sample stored procedure

CREATE procedure dbo.spErrorHandling AS

-- Declaration statements
DECLARE @Error_Number int
DECLARE
@Error_Message varchar(4000)
DECLARE @Error_Severity int
DECLARE
@Error_State int
DECLARE
@Error_Procedure varchar(200)
DECLARE @Error_Line int
DECLARE
@UserName varchar(200)
DECLARE @HostName varchar(200)
DECLARE @Time_Stamp datetime

-- Initialize variables
SELECT @Error_Number = isnull(error_number(),0),
@Error_Message = isnull(error_message(),'NULL Message'),
@Error_Severity = isnull(error_severity(),0),
@Error_State = isnull(error_state(),1),
@Error_Line = isnull(error_line(), 0),
@Error_Procedure = isnull(error_procedure(),''),
@UserName = SUSER_SNAME(),
@HostName = HOST_NAME(),
@Time_Stamp = GETDATE();

-- Insert into the dbo.ErrorHandling table
INSERT INTO dbo.ErrorHandling (Error_Number, Error_Message, Error_Severity, Error_State, Error_Line,
Error_Procedure
, UserName, HostName, Time_Stamp)

SELECT @Error_Number, @Error_Message, @Error_Severity, @Error_State, @Error_Line,
@Error_Procedure
, @UserName, @HostName, @Time_Stamp

Sample logging table

CREATE TABLE [dbo].[ErrorHandling](
[pkErrorHandlingID] [int] IDENTITY(1,1) NOT NULL,
[Error_Number] [int] NOT NULL,
[Error_Message] [varchar](4000) COLLATE Latin1_General_BIN NULL,
[Error_Severity] [smallint] NOT NULL,
[Error_State] [smallint] NOT NULL DEFAULT ((1)),
[Error_Procedure] [varchar](200) COLLATE Latin1_General_BIN NOT NULL,
[Error_Line] [int] NOT NULL DEFAULT ((0)),
[UserName] [varchar](128) COLLATE Latin1_General_BIN NOT NULL DEFAULT (''),
[HostName] [varchar](128) COLLATE Latin1_General_BIN NOT NULL DEFAULT (''),[Time_Stamp] datetime NOT NULL,
PRIMARY KEY CLUSTERED
(
[pkErrorHandlingID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON
[PRIMARY]
GO

Verification process

-- Sample procedure

CREATE PROCEDURE dbo.spTest AS
BEGIN
TRY

SELECT 1/0

END TRY

BEGIN CATCH

EXEC dbo.spErrorHandling

END CATCH

-- Execute the sample procedure
EXEC dbo.spTest;

-- Review the error handling data captured
SELECT *
FROM dbo.ErrorHandling;

Implementation options

Below are some options to implement the standardized error logging:

  • Single table per database
    • Shown in the example above
  • Single table per SQL Server
    • Be sure to qualify the objects with the database name
  • Single table per application if the application is distributed
  • Single table per environment (development, test, production or application type)

Next Steps

  • As you begin to deploy code for SQL Server 2005, think how to architect the error handling portion of the application.  Consider this technique or something custom to meet your organizational error handling needs.
  • With the TRY and CATCH paradigm, the error handling solution in SQL Server can be much easier and more straight forward to implement, so be sure to standardize your code.  Do not settle for any code deployed to production without error handling.
  • When you think about error handling, be sure to think past the development cycles to the operational impacts from the errors.  The additional columns in the table above provide the opportunity to not only capture the error, but support basic issue management as well.
  • For additional information on error handling with SQL Server 2005, check out these tips on MSSQLTips.com:


Last Update:






About the author
MSSQLTips author Jeremy Kadlec Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an Edgewood Solutions SQL Server Consultant, MSSQLTips.com co-founder and Baltimore SSUG co-leader.

View all my tips





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools