Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Error Handling in Nested Procedures and Logging Custom Errors

By Leo Peysakhovich,

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.

Resources:

NonTrans_Template_2005.txt | NonTrans_Template_2005_NEW.txt | Test1_Notrans.txt | Test1_Notrans_NEW.txt | Test2_trans.txt | TransTemplate_2005.txt
Total article views: 8943 | Views in the last 30 days: 9
 
Related Articles
FORUM

Transaction Replication:Distributn Agent Error Could Not find stored procedure 'sp_MSdel_co'

Transaction Replication Error under distribution agent:could not find stored procedure 'sp_MSdel_co'...

FORUM

Error turning Varchar into Numeric

Error turning Varchar into Numeric

FORUM

Stored Procedure with varchar(max) as parameter

Stored Procedure with varchar(max) as parameter

FORUM

Stored procedure error handler

Stored procedure error handler

FORUM

Error Rolling back inner transaction

Error Rolling back inner transaction

Tags
error handling    
logging    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones