Most likely every DBA reading this article already knows that SQL Server 2005 has many new features that include the new error handler. I wrote the article about stored procedure template for SQL Server 2005.
http://www.sqlservercentral.com/articles/Development/anerrorhandlingtemplatefor2005/2295/
In most cases one stored procedure can be a wrapper for one or multiple nested procedures. And most templates are working properly if nested set of procedures are the same kind – all transactional or all non transactional. What is interesting that some errors with nested procedures call are handled very strange way.
Let see some examples. At the beginning, we have to create the error log table to store error and system data. See all explanations in my previous article http://www.sqlservercentral.com/articles/Development/anerrorhandlingtemplatefor2005/2295/
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]
Then 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 parameters.
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 ;
declare @CreateUserName varchar(128),@CreateMachineName varchar(128) ,@CreateSource varchar(128) ;
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),
@$CreateMachineName varchar(128),@$CreateSource varchar(128)
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 error number if not set
IF ISNULL(@$errno,0) = 0
Set @$errno = ERROR_NUMBER();
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;
END CATCH
SET NOCOUNT OFF;
return @$errno;
end
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. The template defined above is the simple template without the ability to run a group of transactional statements. Example of a the transactional template is in file TRANS_TEMPLATE_2005.TXT in the Resource section below.
Let's create simple non transactional procedure and simple transactional procedure with 1 select statement and non transactional procedure calling transactional one. Then execute non transactional procedure. See procedures code inTest1_Notrans.txt and Test2_trans.txt (See the Resources below)
exec dbo.Test1_Notrans;
Output result will have 2 data sets as it expected.
Lets create an error in procedure Test1_Notrans by changing the table name from Employees to Employees1.
Output message as expected is :
Msg 208, Level 16, State 1, Procedure Test1_Notrans, Line 19 Invalid object name 'Northwind.dbo.Employees1'.
Now, change back Employees1 to Employees and make the change in transactional procedure from Employees to Employees1. Run the same statement
exec dbo.Test1_Notrans;
The message is very strange:
Msg 50000, Level 16, State 1, Procedure Test1_Notrans, Line 44
Error 208in proc Test1_Notrans
Msg 50000, Level 16, State 1, Procedure ERROR_LOG_2005, Line 86
Error in proc Error in proc Cannot log error since the current transaction is in an uncommittable state. Rollback the transaction in order to successfully log error information.
Msg 266, Level 16, State 2, Procedure Test1_Notrans, Line 0
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.
Msg 3998, Level 16, State 1, Line 1
Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.
What happened?
If we are adding the print message to the CATCH part of each stored procedure we are going to see that on such error CATCH block is not used by internal stored procedure. But internal procedure failure is captured by external wrapper. Is it Microsoft bug? I don’t know. But it is a problem if you have set of mixed nested procedures where some of them transactional and some are not. What can be done to avoid such situations. I did add very specific block of code into non transactional stored procedure that checking on error if transaction is opened and rollback opened transaction. It is reached by checking the differences between open transactions at the beginning of the procedure and in the CATCH block. If there are 0 opened transactions at the beginning of the procedure and CATCH block has more than 0 opened transactions in non transactional procedure then procedure will rollback transaction because this procedure is a wrapper for the transactional procedure(s) and has the opened transaction(s). If there are opened transactions at the beginning of non transactional procedure then this procedure is in a chain and inside of the transactional wrapper. Then let wrapper take care of transaction. New procedure will looks like this one Test1_Notrans_NEW.txt (See the Resources below). When we run the statement again the message will be as expected.
exec dbo.Test1_Notrans
Msg 50000, Level 16, State 1, Procedure Test1_Notrans, Line 54
Error 208 in proc Test1_Notrans
Now, non transactional template will be like this one NonTrans_Template_2005_NEW.txt (See the Resources below)
It needs to say that this template may not work properly if application code is handling transactions but I think that this is not the case for most modern applications.
Conclusion
For sake of clarity, the template source code is simplified from the actual templates, but all ideas are still remained. I tested these templates with multiple nested mixed transactional and non transactional levels and it working perfect.
