every DBA reading this article already knows that SQL Server 2005 has many new
features that include, the new error handler. Everyone who is working with SQL Server 2000 knows that T-SQL's ability to handle errors is limited and cumbersome. SQL Server 2005 allows one to handle errors much more elegantly and much more limitless
There are bunch of articles about error handlers for SQL Server 2005.
We all know that one basic error in writing a code is not to have error handling in the code. No matter how simple the process may be, the code should recover or bail out of any errors that occur.
By "handle error" we try to anticipate things that might not work as expected. In this
case we would like to write an exception, notify the user and find the cause of
the error. This means that the logging mechanism should be
incorporated in mostly all T-SQL codes. The error handling code should save
the logged error data for proper debugging.
This article is not about an explanation of SQL Server 2005 error handler feature; Instead it shows the examples and explanations of how to implement the standard error handler mechanism for handling the errors in SQL Server 2005. As a DBA, I prefer using the standardized procedure template for the entire company. This is why I created the stored procedure templates for transactional and non transactional procedures. In an ideal world error handling must be simple, incomplete transactions must never be committed and any uncommitted or non rollback transactions should never be left. Stored procedure should not rollback a
transaction that was started by a parent procedure (caller).
Let’s see how it was done. This article is going to present two stored procedure templates. They handle non transactional and transactional procedures that satisfy the basic conditions for error handing outlined above.
At the beginning, I created error log table to store error and system data.
CREATE TABLE ERROR_LOG ( [ERROR_LOG_ID] [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY , [ERROR_LOG_PROGRAM_NM] [varchar] (128) NULL , -- ERROR_PROCEDURE() [ERROR_LINE_NO] INT NULL, -- ERROR_LINE() [ERROR_LOG_ERROR_NO] [int] NULL , -- ERROR_NUMBER() [ERROR_LOG_ERROR_DSC] [varchar] (4000) NULL , -- ERROR_MESSAGE() [ERROR_SEVERITY_NO] INT NULL, -- ERROR_SEVERITY() [ERROR_STATE_NO] INT NULL, -- ERROR_STATE() [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 ) ALTER TABLE ERROR_LOG ADD CONSTRAINT [CURRDT] DEFAULT (getdate()) FOR [CreateDate], CONSTRAINT [CURRUSER] DEFAULT (suser_sname()) FOR [CreateUserName], CONSTRAINT [HOSTNM] DEFAULT (host_name()) FOR [CreateMachineName], CONSTRAINT [PROCESSNM] DEFAULT (@@procid) FOR [CreateSource]
The following system functions to acquire information about
errors within the CATCH block. The following functions, if called outside the CATCH
block, will return NULL.
ERROR_LINE() returns the line number at which the error occurred.
ERROR_MESSAGE() returns the text of the message that would
be returned to the application. The text includes the values supplied for any
substitutable parameters, such as lengths, object names, or times.
ERROR_NUMBER() returns the error number.
ERROR_PROCEDURE() returns the name of the stored procedure
or trigger in which the error occurred. This function returns NULL if the error
did not occur inside a stored procedure or trigger.
ERROR_SEVERITY() returns the severity.
ERROR_STATE() returns the state.
Then I created a standard stored procedure that will load an error tracked information to the table ERROR_LOG. The stored procedure takes information provided by the code, grabs and stores the information the last statement sent from a client to Microsoft® SQL Server by utilizing DBCC INPUTBUFFER command. It gives the ability to store the set of parameters to the stored procedure where error has happened. In many cases it helped me debug a stored procedure and find the very specific error, which appeared only with specific set of input parameter’s.
SET QUOTED_IDENTIFIER OFF GO 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 declare @prog varchar(128), @errno int, @errmsg varchar(4000), @proc_section_nm varchar(255), @cmd varchar(50), @errline int, @errstate int, @errseverity int, @INSERTED_IDENTITY_ID int -- Contains the ErrorLogID of the row inserted in the ERROR_LOG table. declare @CreateUserName varchar(128), -- last user changed the data @CreateMachineName varchar(128) , -- last machine changes-procedure were run from @CreateSource varchar(128) -- last process that made a changes SET NOCOUNT ON -- Output parameter value of 0 indicates that error information was not logged SET @INSERTED_IDENTITY_ID = 0; -- for the logical/business error there is no system error -- business error will be passed as parameter from stored procedure IF (ISNULL(@ERROR_LOG_ERROR_NO,0) = 0) begin set @ERROR_LOG_ERROR_NO = ERROR_NUMBER(); set @errseverity = ERROR_SEVERITY(); set @errstate = ERROR_STATE(); set @prog = ERROR_PROCEDURE(); set @errline = ERROR_LINE(); set @ERROR_LOG_ERROR_DSC = left(ltrim( IsNull(@ERROR_LOG_ERROR_DSC,'')) + ';' + ERROR_MESSAGE(), 4000); end set @CreateUserName = CONVERT(sysname, CURRENT_USER); set @CreateMachineName = host_name(); set @CreateSource = isnull(object_name(@@procid), ''); BEGIN TRY -- Return if there is no error information to log IF ( ISNULL(@ERROR_LOG_ERROR_NO,0) = 0 ) 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 set @errmsg = 'Cannot log error since the current transaction is in an uncommittable state. ' + 'Rollback the transaction in order to successfully log error information.'; set @errmsg = 'Error in proc ' + isnull(@prog,' ') + ' ' + isnull(@errmsg,' '); raiserror (@errmsg, 16, 1); RETURN -1; END; SET @cmd = "DBCC INPUTBUFFER( " + CAST(@@spid as varchar) + ")"; insert into ERROR_LOG ( ERROR_LOG_EVENT, ERROR_LOG_PARAMETER, ERROR_LOG_EVENT_INFO ) exec (@cmd); set @INSERTED_IDENTITY_ID = IDENT_CURRENT('HIST_ERROR_LOG'); update dbo.ERROR_LOG SET CreateUserName = @CreateUserName, CreateMachineName = @CreateMachineName, CreateSource = @CreateSource, ERROR_LOG_PROGRAM_NM = @ERROR_LOG_PROGRAM_NM, ERROR_LOG_PROGRAM_SECTION_NM = @ERROR_LOG_PROGRAM_SECTION_NM, ERROR_LOG_ERROR_NO = @ERROR_LOG_ERROR_NO, ERROR_LOG_ERROR_DSC = @ERROR_LOG_ERROR_DSC, ERROR_LOG_SPID_NO = @@SPID, ERROR_DB_NAME = @ERROR_DB_NAME, ERROR_LINE_NO = @errline, ERROR_SEVERITY_NO = @errseverity, ERROR_STATE_NO = @errstate WHERE ERROR_LOG_ID = @INSERTED_IDENTITY_ID END TRY BEGIN CATCH set @errmsg = 'Error in proc ' + isnull(@prog,' ') + ' ' + isnull(@errmsg,' '); raiserror(@errmsg, 16,1); RETURN -1; END CATCH; RETURN @ERROR_LOG_ERROR_NO; End -- End of stored procedure
Next step is the template creation.
SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO CREATE procedure dbo.TEMPLATE_2005 as begin SET NOCOUNT ON declare @$prog varchar(50), @$errno int, @$errmsg varchar(4000), @$proc_section_nm varchar(50), @$row_cnt INT, @$error_db_name varchar(50), @$CreateUserName varchar(128), -- last user changed the data @$CreateMachineName varchar(128) -- last machine changes-procedure were run from @$CreateSource varchar(128) -- last process that made a changes select @$errno = NULL, @$errmsg = NULL, @$proc_section_nm = NULL , @$prog = LEFT(object_name(@@procid),50), @$row_cnt = NULL , @$error_db_name = db_name(); --========= BEGIN TRY --========= -- User Source code --======== END TRY --======== BEGIN CATCH --=========== set @$errmsg = Left('Error ' + CASE WHEN @$errno > 0 THEN CAST(@$errno as varchar) ELSE Cast(ERROR_NUMBER() as varchar) END + 'in proc ' + isnull(@$prog,' ') + ' ' + CASE WHEN @$errno > 0 THEN isnull(@$errmsg,' ') ELSE isnull(@$errmsg,' ') + ISNULL(ERROR_MESSAGE(),'') END ,4000); raiserror (@$errmsg, 16, 1); EXEC dbo.ERROR_LOG_2005 @ERROR_LOG_PROGRAM_NM = @$prog, @ERROR_LOG_PROGRAM_SECTION_NM = @$proc_section_nm, @ERROR_LOG_ERROR_NO = @$errno, @ERROR_LOG_ERROR_DSC = @$errmsg, @ERROR_DB_NAME = @$error_db_name -- set the error if not set IF (ISNULL(@$errno,0) = 0 ) set @$errno = ERROR_NUMBER(); END CATCH SET NOCOUNT OFF; return @$errno; end
Here are some additional comments to the template. If you
would like to control precisely the statement or the sections where an error
happened for the purpose of easier debugging the code then the next statement should
be in front of the user defined section or before each SQL statement. For
set @$proc_section_nm = 'Section: 20';
The system can raise errors when it encounters a problem,
but we can also raise our own errors. The T-SQL command "RAISERROR"
returns an error code of our choice, along with a standard or custom level and
message. Our code can then check for these levels and messages and handle them
accordingly. In my opinion, error handler should not only handle the
system errors such as can’t UPDATE, INSERT, DELETE, SELECT and so on, but also handle the business' logical errors. For example, selection of zero rows is a perfectly valid result for the statement but can be considered as an error from a business prospective and may require handling like an inability
to retrieve the record. So, if you would like to get the information on how many rows have been retrieved then inside a stored procedure the next statement should be placed as a first statement after
the SQL statement.
set @$row_cnt = @@ROWCOUNT;
If we would like to capture a logical error then use the next set of statements. This will send stored procedure to BEGIN CATCH block. For example:
Select user_no, user_id from TABLE_PERSON where user_id = 5; set @$row_cnt = @@ROWCOUNT; IF (@$row_cnt = 0) BEGIN set @$errno = 100000; -- Any user defined error number over 50000 set @$errmsg = 'Error message'; raiserror (@$errmsg, 16, 1); END
In addition, the template can have an application or company specific variables or parameters. For example, my template and error handler has inputted parameters for the processes because in our case each back end process has its own id. This is allows DBA to quickly find the error when process stored procedures are shared among the processes.
The template defined above is the simple template without the ability to run a group of transactional statements. Let’s see the differences between non transactional and transactional templates.
- There is a check if procedure is inside of the parent transaction at the beginning of the stored procedure after the variable declaration.
Declare @$tran_flag; set @$tran_flag = @@trancount;
- The next statement should be placed as close to the beginning of data changes as possible. Some times I place the next statement right after the statement above when the stored procedure run time is short. But the statement can be moved and then it will change the place where the transaction is going to be started. The flag is the indicator that a transaction has started in this stored procedure when changes will be COMMITED or ROLLBACK at the end of the stored procedure. This is necessary when the statement is moved from the beginning of the procedure down.
if (@$tran_flag = 0) begin begin transaction procmain_tran; set @$tran_started_flag = 'Y'; end
- COMMIT the transaction if no errors with next statement as the last statement before END TRY.
IF (@$tran_started_flag = 'Y') begin COMMIT TRANSACTION ; end
- ROLLBACK transaction inside of TRY CATCH – END CATCH block with next statement
IF (@$tran_started_flag = 'Y') begin ROLLBACK TRANSACTION; end IF (@$tran_flag = 0) begin EXEC dbo.ERROR_LOG @ERROR_LOG_PROGRAM_NM = @$prog, @ERROR_LOG_PROGRAM_SECTION_NM = @$proc_section_nm, @ERROR_LOG_ERROR_NO = @$errno, @ERROR_LOG_ERROR_DSC = @$errmsg, @ERROR_DB_NAME = @$error_db_name, End
Example of a the transactional template is in file TRANS_TEMPLATE_2005.TXT
For sake of clarity, the template source code is simplified from the actual templates ,but all ideas are still remained. I have used these templates for a few months and I am still using them for SQL Server 2005 in development and production environment. It perfectly serves the need for all my application’s and back end processing stored procedures and it has been adopted as a standard for all SQL Server developers.