An ErrorHandling Template

Most likely 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 example:
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

My first .Net Stored Procedure

This is my first attempt to use the .Net feature of Yukon. I tried this on the CTP versions of both SQL server 2005 and Visual Studio 2005; both are available on microsoft website

This program will return the Count(*) from a table

Pre -Requisites

1. Create a Database TestDB

CREATE DATABASE TestDB
GO
USE TestDB
GO

2. Now create a table 'Authors' and insert some values

3. Create a new C# Class library project
4. Inside that default template , create a new method ->GetCount()

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using Microsoft.SqlServer;
namespace ClassLibrary1
{
public class Class1
{
public Class1() { }
public static int GetCount()
{
using (SqlConnection cn = new SqlConnection("context connection = true"))
{
cn.Open();
int iRows;
SqlCommand sqlCmd = new SqlCommand(
"select count(*) as 'Count' from Authors", cn);
iRows = (int)sqlCmd.ExecuteScalar();
return iRows;
}
}
}
}


5. Compile the CLR stored procedure and build the dll. Note the path; by default it will be in 'My Documents\Visual Studio 2005\Projects' folder.
6. Create a Folder 'TestProjects' under 'C:\' and copy the dll into it.
7. That's all from Visual Studio.Net Side
8. Back to SQL server 2005, Create an assembly

CREATE ASSEMBLY asmClass
FROM 'C:\TestProjects\ClassLibrary1.dll'
GO

The CREATE ASSEMBLY command takes a parameter that contains the path to the DLL that will be loaded into SQL Server. This can be a local path but more often it is a path to a networked file share. When the CREATE ASSEMBLY command is executed, the DLL is copiedinto the master database.

9. Now create a procedure


CREATE PROCEDURE FirstPgm
AS
EXTERNAL NAME
asmClass.[ClassLibrary1.Class1].GetCount
GO


The EXTERNAL NAME statement is new to SQL Server Yukon. In the preceding example, the EXTERNAL NAME statement specifies that the stored procedure will be created using a .NET assembly. An assembly can contain multiple namespaces and methods, and the EXTERNAL NAME statement uses the following syntax to identify the correct code to use fromwithin the assembly:
Assembly Name:[AssemblyNamespace.TypeName]::MethodName

10. Now run the Procedure

declare @x int
Exec @x = First
Print @x

By default clr won't be enabled in SQL server 2005. If you are getting this kind of error while running the above, u need to configure 'Clr' in SQL. For this run the following

EXEC sp_configure 'show advanced options' , '1';
GO
reconfigure;
GO
EXEC sp_configure 'clr enabled' , '1'
go
reconfigure;
-- Turn advanced options back off
EXEC sp_configure 'show advanced options' , '0';
GO
reconfigure;
GO

That's all ...