Building the SQL Error Engine
In this second post focused on a T-SQL framework (find part one here), we will build the plumbing to leverage our first feature of the framework, the error engine. This feature will runs like an engine and do the manual work we do not want to do. To make it dynamic, the feature must be data-driven using small tools that work with each other.
We will address architecture, database design, functions, and other coding principles, following some best practices. We are not going to describe each one in detail because that is out of scope for this project. That would make this post even longer. If you find that we have glanced over any topic that you feel should be discussed in more detail, please leave some feedback. If there is enough interest, we may publish a blog solely on that topic.
With this blog post, we will:
- Create a new database to hold our framework
- Create code tables, an error definition table, and an error logging table
- Populate our base data-driven tables (the code tables and the error definition table)
- Create a user-defined function to get our codes for us
- Add a new error message within the SQL engine
- Create a new template that will leverage the coding effort above
A lot of work is ahead of us, and there won’t necessarily be a lot of explanation. For instance, we will create a database that will hold all stored procedures that will be leveraged to execute against your user databases. Why not just develop this in the user database for ease? Several reasons:
- We want to develop this once and only once. We come from an environment where one SQL developer may be tasked with writing for six .net developers and we don’t want to introduce confusion, so just one connection string. The developers make the connection to the code database, and the stored procs that are executed in that code database will be executed against the user data that returns result sets accordingly. In short, all roads lead to Rome.
- If all our security is set for an empty database with just a bunch of code, if someone were to gain access uninvited, what do they have? And they have even less if that code is encrypted.
- It forces the DBA to use a three-part naming scheme of database name, schema name, and object name. While this may look like it takes more time referencing each object in the database, we have noticed the precision results in a dramatic decrease in troubleshooting time. You get used to the extra typing (especially since intelligence is your friend), and when you are developing enterprise solutions you don’t want to be called out for being bush league when a few more seconds make you into an enterprise surgical developer (right?).
You can choose any name you wish to call the database. At SQL Solutions Group, we develop several internal products against the database we are calling DBCatch, so we will be using that. You can also use the name DBCatch and we encourage that. Just as long as you do not sell anything under that name without discussing that with us and the copyright thereof.
Create Database and Tables
We are not going to get too fancy with the database options; keeping it simple with this code:
CREATE DATABASE DBCatch;
GO
Great, the first task is complete. Next, we need to create a few tables. The first set of tables will be the code tables. I do not like creating different code tables for every function that comes up, so I create two tables that act as a kind of named value pair. That way, these two tables take care of almost all my code definition needs. The first table is the Code Group (this is the header), and the second is the Code (this is the detail). Let’s just jump in and you will see what I am talking about once we populate the tables and you can see them in action.
USE DBCatch;
GO
CREATE TABLE [dbo].[TU_CodeGroup]
(
[CodeGroup_ID] [INT] IDENTITY (1, 1) NOT NULL
CONSTRAINT [PK_CodeGroup_ID]
PRIMARY KEY CLUSTERED
,[Name] [NVARCHAR](100) NOT NULL
,[DESC] [NVARCHAR](255) NOT NULL
CONSTRAINT DF_TU_CodeGroup_Desc
DEFAULT ('Group Code')
,[Active] [BIT] NOT NULL
CONSTRAINT DF_TU_CodeGroup_Active
DEFAULT ((1))
,[DateCreated] [DATETIME] NOT NULL
CONSTRAINT DF_TU_CodeGroup_DateCreated
DEFAULT (GETDATE())
,[DateModified] [DATETIME] NOT NULL
CONSTRAINT DF_TU_CodeGroup_DateModified
DEFAULT (GETDATE())
) ON [PRIMARY];
GO
CREATE TABLE [dbo].[TU_Code]
(
[Code_ID] [INT] IDENTITY (1, 1) NOT NULL
CONSTRAINT [PK_Code_ID]
PRIMARY KEY CLUSTERED
,[CodeGroup_ID] [INT] NOT NULL
CONSTRAINT [FK_TU_Code_CodeGroup_ID]
FOREIGN KEY ([CodeGroup_ID])
REFERENCES [TU_CodeGroup]([CodeGroup_ID])
,[Name] [NVARCHAR](100) NOT NULL
,[DESC] [NVARCHAR](255) NOT NULL
CONSTRAINT [DF_TU_Code_Desc]
DEFAULT 'Code'
,[Active] [BIT] NOT NULL
CONSTRAINT [DF_TU_Code_Active]
DEFAULT 1
,[DateCreated] [DATETIME] NOT NULL
CONSTRAINT [DF_TU_Code_DateCreated]
DEFAULT GETDATE()
,[DateModified] [DATETIME] NOT NULL
CONSTRAINT [DF_TU_Code_DateModified]
DEFAULT GETDATE()
) ON [PRIMARY];
GO
Naming Scheme and Populating Tables
At SSG, we adhere to a specific naming scheme. I recommend anyone that develops code should adopt a naming scheme that works, for you and your company. The prefix TU_ above in front of the table names stands for user table. SSG does a lot of data dictionary work and when we describe our objects, we like them to be grouped nicely. This way we can describe each set of objects (like tables, views, procedures, etc.) in sets visually. Now that our code tables are in the DBCatch database, populate them with the data we need:
USE DBCatch;
GO
SET IDENTITY_INSERT TU_CodeGroup ON;
INSERT [DBCATCH].[dbo].[TU_CodeGroup]
( CodeGroup_ID, Name, [DESC])
SELECT 1, 'ERROR', 'ERROR CODES';
SET IDENTITY_INSERT TU_CodeGroup OFF;
SET IDENTITY_INSERT TU_Code ON;
INSERT [DBCATCH].[dbo].[TU_Code]
( Code_ID, CodeGroup_ID, Name, [DESC])
SELECT 100, 1, 'DEFAULT', 'UNKNOWN ERROR'
UNION
SELECT 101, 1, 'SQL', 'INTERNAL SQL SERVER RELATED ERROR'
UNION
SELECT 102, 1, 'DBCATCH', 'DBCATCH RELATED ERROR'
UNION
SELECT 103, 1, 'ESCLATION', 'ERROR THAT HAS BEEN ESCALATED TO A PARENT ERROR'
UNION
SELECT 104, 1, 'MAINTENANCE', 'DATABASE MAINTENANCE RELATED ERROR';
SET IDENTITY_INSERT TU_Code OFF;
You can see the base data we just populated, with the two select statements below. This will make more sense once we create the user-defined function and actually start using it in the template.
USE DBCatch;
GO
SELECT [CodeGroup_ID]
,[Name]
,[DESC]
,[Active]
,[DateCreated]
,[DateModified]
FROM [DBCatch].[dbo].[TU_CodeGroup];
SELECT [Code_ID]
,[CodeGroup_ID]
,[Name]
,[DESC]
,[Active]
,[DateCreated]
,[DateModified]
FROM [DBCatch].[dbo].[TU_Code];
Trapping Errors with the SQL Error Engine
Now, let’s dive into the user-defined scalar function. This retrieves the code IDs for us when we use them programmatically within our solution. The idea here is that we will be trapping any errors that come up by a try catch method inside our T_SQL code. Once we capture the error, we categorize it and define it ourselves. This way, the error is not cryptic and we can define it in a way that will point us to the issue. This cuts down our troubleshooting time.
First, we’ll create the function. Next, we will continue making this solution more customized.
USE DBCATCH;
GO
SET NOCOUNT ON;
IF EXISTS(SELECT 1
FROM [INFORMATION_SCHEMA].[ROUTINES] (NOLOCK)
WHERE [SPECIFIC_SCHEMA] = 'dbo'
AND [SPECIFIC_NAME] = 'FSU_Code_GET')
BEGIN
DROP FUNCTION [dbo].[FSU_Code_GET];
PRINT '<<< DROPPED FUNCTION [dbo].[FSU_Code_GET] >>>';
END;
GO
CREATE FUNCTION [dbo].[FSU_Code_GET]
(
@p_CodeGroup [NVARCHAR](100)
,@p_Code [NVARCHAR](100)
)
RETURNS INT
WITH ENCRYPTION
AS
BEGIN
/************************************************************************/
/* Name : FSU_Code_GET */
/* Version : 1.0 */
/* Author : Jared Kirkpatrick */
/* Date : 2019-07-24 */
/* Description : Get the value from the code table. */
/************************************************************************/
/* Date : Version: Who: Description */
/************************************************************************/
/* 2019-05-24 : 1.0 : JSK: Initial Release. */
/************************************************************************/
DECLARE @v_Return [INT];
SELECT @v_Return = b.[Code_ID]
FROM [DBCATCH].[dbo].[TU_CodeGroup] a
JOIN [DBCATCH].[dbo].[TU_Code] b
ON a.[CodeGroup_ID] = b.[CodeGroup_ID]
WHERE a.[Name] = @p_CodeGroup
AND b.[Name] = @p_Code;
RETURN @v_Return;
END;
GO
IF OBJECT_ID('[dbo].[FSU_Code_GET]') IS NOT NULL
BEGIN
PRINT '<<< CREATED FUNCTION [dbo].[FSU_Code_GET] >>>';
END;
ELSE
BEGIN
PRINT '<<< FAILED TO CREATE FUNCTION [dbo].[FSU_Code_GET] >>>';
END;
GO
To see what we have done here, let’s look at the two tables we first populated, TU_CodeGroup and TU_Code.
The TU_CodeGroup table has one record, and the value (actually, the {Name} field), is “ERROR”. In the TU_Code table we will be mostly using the code_id that corelates to the “DBCatch” value since we are aiming at categorizing errors we trap from DBCatch. If we want the code for DBCatch errors, we use our function which takes the value of the code group (Error) and the value of the code (“DBCatch”) and we retrieve it by leveraging the function as shown below which will give us the value 102…
USE DBCatch;
GO
SELECT dbo.[FSU_Code_GET]('Error', 'DBCatch');
Next, we create the table that will hold the error definition, and then we will populate those definitions.
USE DBCatch;
GO
CREATE TABLE [dbo].[TU_Error]
(
[Error_ID] [INT] IDENTITY (1, 1) NOT NULL
CONSTRAINT [PK_Error_ID]
PRIMARY KEY CLUSTERED
,[Type_ID] [INT] NOT NULL
CONSTRAINT [FK_TU_Error_Code_ID]
FOREIGN KEY ([Type_ID])
REFERENCES [TU_Code]([Code_ID])
,[Name] [NVARCHAR](100) NOT NULL
,[Severity] [INT] NULL
,[STATE] [INT] NULL
,[DESC] [NVARCHAR](255) NOT NULL
CONSTRAINT [DF_TU_Error_Desc]
DEFAULT 'Error'
,[Active] [BIT] NOT NULL
CONSTRAINT [DF_TU_Error_Active]
DEFAULT 1
,[DateCreated] [DATETIME] NOT NULL
CONSTRAINT [DF_TU_Error_DateCreated]
DEFAULT GETDATE()
,[DateModified] [DATETIME] NOT NULL
CONSTRAINT [DF_TU_Error_DateModified]
DEFAULT GETDATE()
) ON [PRIMARY];
USE DBCatch;
GO
SET IDENTITY_INSERT TU_Error ON;
INSERT [DBCATCH].[dbo].[TU_Error]
( Error_ID, [Type_ID], [Name], [Severity], [STATE], [DESC])
SELECT 100000, 100, 'DEFAULT_DBCATCH', '16', '1', 'UNKNOWN ERROR IN DBCATCH'
UNION
SELECT 100001, 102, 'DBCATCH_INSERT', '16', '1', 'ERROR INSERTING INTO A DBCATCH TABLE'
UNION
SELECT 100002, 102, 'DBCATCH_UPDATE', '16', '1', 'ERROR UPDATING A DBCATCH TABLE'
UNION
SELECT 100003, 102, 'DBCATCH_DELETE', '16', '1', 'ERROR DELETING FROM A DBCATCH TABLE'
UNION
SELECT 100004, 102, 'DBCATCH_SELECT', '16', '1', 'ERROR DBCATCH DATA COLLECTOR (SELECTING INFO)';
SET IDENTITY_INSERT TU_Error OFF;
GO
The final step for the definition is to add an error message in the SQL engine, but we are going to put a twist on this so it is not exactly straightforward. We set the message to be defined at runtime so that we can customize the error message and manipulate. This helps us understand what is happening.
When we get to the template that shows how this works, it will be clear. It is hard to describe what you cannot see, but we will have a demo at the end so that you can see it in action. Then the light bulbs will go off in that “a-ha!” moment.
Let’s make some error messages!
USE DBCatch;
GO
IF EXISTS(SELECT 1
FROM [master].[sys].[messages]
WHERE [message_id] = 100000)
BEGIN
EXEC [DBCATCH].[dbo].[SP_DROPMESSAGE] @msgnum = 100000;
END;
EXEC [DBCATCH].[dbo].[SP_ADDMESSAGE]
@msgnum = 100000,
@severity = 16,
@msgtext = N'%s';
IF EXISTS(SELECT 1
FROM [master].[sys].[messages]
WHERE [message_id] = 100001)
BEGIN
EXEC [DBCATCH].[dbo].[SP_DROPMESSAGE] @msgnum = 100001;
END;
EXEC [DBCATCH].[dbo].[SP_ADDMESSAGE]
@msgnum = 100001,
@severity = 16,
@msgtext = N'%s';
IF EXISTS(SELECT 1
FROM [master].[sys].[messages]
WHERE [message_id] = 100002)
BEGIN
EXEC [DBCATCH].[dbo].[SP_DROPMESSAGE] @msgnum = 100002;
END;
EXEC [DBCATCH].[dbo].[SP_ADDMESSAGE]
@msgnum = 100002,
@severity = 16,
@msgtext = N'%s';
IF EXISTS(SELECT 1
FROM [master].[sys].[messages]
WHERE [message_id] = 100003)
BEGIN
EXEC [DBCATCH].[dbo].[SP_DROPMESSAGE] @msgnum = 100003;
END;
EXEC [DBCATCH].[dbo].[SP_ADDMESSAGE]
@msgnum = 100003,
@severity = 16,
@msgtext = N'%s';
IF EXISTS(SELECT 1
FROM [master].[sys].[messages]
WHERE [message_id] = 100004)
BEGIN
EXEC [DBCATCH].[dbo].[SP_DROPMESSAGE] @msgnum = 100004;
END;
EXEC [DBCATCH].[dbo].[SP_ADDMESSAGE]
@msgnum = 100004,
@severity = 16,
@msgtext = N'%s';
GO
One Last Thing….
Before we create our template for the SQL error engine, we create one more table. This holds our error information that will be logged. We also create a simple stored proc to insert into that table. Any time we have an error we will log that error into this table. If you have the stored proc in management studio, that is great to see the error printed for you. But, in most cases a process will be running when you are not looking. Having this log will be helpful because we can look at it after the fact and see what happened. The code for the error log table is:
USE DBCatch;
GO
CREATE TABLE [dbo].[TU_ErrorLog]
(
[ErrorLog_ID] [INT] IDENTITY (1, 1) NOT NULL
CONSTRAINT [PK_ErrorLog_ID]
PRIMARY KEY CLUSTERED
,[Error_ID] [INT] NOT NULL
CONSTRAINT [FK_TU_ErrorLog_Error_ID]
FOREIGN KEY ([Error_ID])
REFERENCES [TU_Error]([Error_ID])
,[System] [NVARCHAR](100) NULL
,[DB] [NVARCHAR](100) NULL
,[Obj] [NVARCHAR](100) NULL
,[App] [NVARCHAR](100) NULL
,[USER] [NVARCHAR](100) NULL
,[SPID] [NVARCHAR](100) NULL
,[ErrorMsg] [NVARCHAR](2500) NULL
,[Active] [BIT] NOT NULL
CONSTRAINT [DF_TU_ErrorLog_Active]
DEFAULT 1
,[DateCreated] [DATETIME] NOT NULL
CONSTRAINT [DF_TU_ErrorLog_DateCreated]
DEFAULT GETDATE()
,[DateModified] [DATETIME] NOT NULL
CONSTRAINT [DF_TU_ErrorLog_DateModified]
DEFAULT GETDATE()
) ON [PRIMARY];
GO
USE DBCATCH
GO
IF EXISTS(SELECT 1
FROM [dbo].[sysobjects] (NOLOCK)
WHERE [id] = OBJECT_ID(N'[dbo].[PU_ErrorLog_ADD]')
AND OBJECTPROPERTY([id], N'IsProcedure') = 1)
BEGIN
DROP PROCEDURE [dbo].[PU_ErrorLog_ADD];
PRINT '<<< DROPPED PROCEDURE [dbo].[PU_ErrorLog_ADD] >>>';
END
GO
CREATE PROC [dbo].[PU_ErrorLog_ADD]
@p_Error_ID [INT] = NULL,
@p_System [NVARCHAR](100) = NULL,
@p_DB [NVARCHAR](100) = NULL,
@p_Obj [NVARCHAR](100) = NULL,
@p_App [NVARCHAR](100) = NULL,
@p_User [NVARCHAR](100) = NULL,
@p_SPID [NVARCHAR](100) = NULL,
@p_ErrorMsg [NVARCHAR](2500) = NULL
WITH ENCRYPTION
AS
/*************************************************************************************************/
/* Name : PU_ErrorLog_ADD */
/* Version : 1.0 */
/* Author : Jared Kirkpatrick */
/* Date : 2019-07-24 */
/* Description : Inserts an error into the errorlog table */
/*************************************************************************************************/
/* Date : Version: Who: Description */
/*************************************************************************************************/
/* 2019-07-24 : 1.0 : REB: Initial Release. */
/*************************************************************************************************/
SET NOCOUNT ON;
/* Declare Variables */
DECLARE @v_Error [INT]
,@v_MSG [NVARCHAR](2500)
IF @p_Error_ID IS NULL
BEGIN
SELECT @p_Error_ID = 0;
END
BEGIN TRANSACTION
INSERT [DBCATCH].[dbo].[TU_ErrorLog]
(
[Error_ID],
[System],
[DB],
[Obj],
[App],
[USER],
[ErrorMsg],
[SPID]
)
VALUES
(
@p_Error_ID,
@p_System,
@p_DB,
@p_Obj,
@p_App,
@p_User,
@p_ErrorMsg,
@p_SPID
)
SELECT @v_Error = @@ERROR;
IF @v_Error <> 0
BEGIN
RAISERROR('INSERT into TU_ErrorLog Failed!', 16, 1)
ROLLBACK TRANSACTION
RETURN @v_Error;
END;
COMMIT TRANSACTION;
GO
IF OBJECT_ID('[dbo].[PU_ErrorLog_ADD]') IS NOT NULL
BEGIN
PRINT '<<< CREATED PROCEDURE [dbo].[PU_ErrorLog_ADD] >>>';
END;
ELSE
BEGIN
PRINT '<<< FAILED TO CREATE PROCEDURE [dbo].[PU_ErrorLog_ADD] >>>';
END;
GO
If you have been following along, your DBCatch database should have the following objects when you look at it in Management Studio:
Producing the Template
We’ve put it all together. Now we can create (or better yet, save off) a template that has the feature we just created in a stored procedure. Before we get into the template, we will create additional features, so this template will change as we develop.
USE DBCATCH
GO
IF EXISTS(SELECT 1
FROM [dbo].[sysobjects] (NOLOCK)
WHERE [id] = OBJECT_ID(N'[dbo].[<Name, SYSNAME, PU_DB_ADD>]')
AND OBJECTPROPERTY([id], N'IsProcedure') = 1)
BEGIN
DROP PROCEDURE [dbo].[<Name, SYSNAME, PU_DB_ADD>];
PRINT '>>> DROPPED PROCEDURE [dbo].[<Name, SYSNAME, PU_DB_ADD>] <<<';
END
GO
CREATE PROC [dbo].[<Name, SYSNAME, PU_DB_ADD>]
WITH ENCRYPTION
AS
/******************************************************/
/* Name : <Name, SYSNAME, PU_DB_ADD> */
/* Version : 1.0 */
/* Author : */
/* Date : 2019-07-24 */
/* Description : */
/******************************************************/
/* Date : Version: Who: Description */
/******************************************************/
/* 2019-07-24 : 1.0 : : Initial Release. */
/******************************************************/
SET NOCOUNT ON;
/* Declare Variables */
DECLARE @v_Error [INT]
,@v_MSG [NVARCHAR](500)
,@v_DB [NVARCHAR](100)
,@v_Obj [NVARCHAR](100)
,@v_App [NVARCHAR](100)
,@v_User [NVARCHAR](100)
,@v_Spid [NVARCHAR](100)
,@v_ActivityLog_ID [INT]
,@v_ADesc [NVARCHAR](512)
,@v_Now [DATETIME]
,@v_LastErrorDate [DATETIME]
,@v_SystemName [NVARCHAR](100)
,@v_RowCount [INT]
,@v_Count [INT]
,@v_MinINT [INT]
,@v_MaxINT [INT]
,@v_Version [INT]
,@v_KeyWord [SYSNAME]
,@v_MSSQL_ID [INT]
,@v_Domain [NVARCHAR](100)
,@v_Batch_Type_ID [INT]
,@v_System [NVARCHAR](100)
,@v_SQL [NVARCHAR](4000)
,@v_UserName [NVARCHAR](100)
,@v_PW [NVARCHAR](100)
,@v_Debug [INT];
SELECT @v_Now = GETDATE()
,@v_DB = DB_NAME()
,@v_Obj = '[DBCATCH].[dbo].[<Name, SYSNAME, PU_DB_ADD>]'
,@v_App = APP_NAME()
,@v_User = ISNULL(ORIGINAL_LOGIN(), USER_NAME())
,@v_Spid = CONVERT([NVARCHAR](25), @@SPID)
,@v_System = HOST_NAME()
,@v_Debug = 0;
/* Create Temp Tables */
IF @v_Debug = 1
BEGIN -- debug = 1 Ref: 1
SELECT 'REF:1 - Define Static Variables';
SELECT @v_Now AS [GETDATE]
,@v_DB AS [DB]
,@v_Obj AS [OBJ]
,@v_App AS [APP]
,@v_User AS [USER]
,@v_Spid AS [SPID];
END; -- debug = 1
BEGIN TRY
--==>> Start Code Here!
END TRY
BEGIN CATCH
SELECT @v_Error = 100000
,@v_MSG = CONVERT(NVARCHAR(2500),
'Error: ' + CONVERT([NVARCHAR](255), ISNULL(ERROR_NUMBER(), -1)) +
' Severity: ' + CONVERT([NVARCHAR](255), ISNULL(ERROR_SEVERITY(), -1)) +
' State: ' + CONVERT([NVARCHAR](255), ISNULL(ERROR_STATE(), -1)) +
' Line: ' + CONVERT([NVARCHAR](255), ISNULL(ERROR_LINE(), -1)) +
' Procedure: ' + CONVERT([NVARCHAR](255), ISNULL(ERROR_PROCEDURE(), @v_Obj)) +
' MSG: Error from [DBCATCH].[dbo].[TU_Errorlog] with error:' + ISNULL(ERROR_MESSAGE(), ''));
EXEC [DBCATCH].[dbo].[PU_ErrorLog_ADD]
@p_Error_ID = @v_Error,
@p_System = @v_System,
@p_DB = @v_DB,
@p_Obj = @v_Obj,
@p_App = @v_App,
@p_User = @v_User,
@p_ErrorMsg = @v_MSG;
SELECT @v_Msg;
RETURN @v_Error;
END CATCH;
EOF:
/* Cleanup */
GO
IF OBJECT_ID('[dbo].[<Name, SYSNAME, PU_DB_ADD>]') IS NOT NULL
BEGIN
PRINT '>>> CREATED PROCEDURE [dbo].[<Name, SYSNAME, PU_DB_ADD>] <<<';
END;
ELSE
BEGIN
PRINT '>>> FAILED TO CREATE PROCEDURE [dbo].[<Name, SYSNAME, PU_DB_ADD>] <<<';
END;
GO
Yep, it’s a lot when you first look at it, but we are going to go through it and it will be manageable. If you work with it every day, it will become second nature. The first thing we need to do is change the template parameters. This is embedded in the template and looks like this:
This is the code from the very top, and instead of the actual procedure name we see:
[<Name, SYSNAME, PU_DB_ADD>]
To change this parameter, we simultaneously hold down the keys SHIFT/CTRL/M and then type in the name of the stored procedure we are creating. For instance, if we want to create a demo, we could type PU_Demo1 (the prefix PU stands for user defined procedure).
After you have changed the parameters, go down about 35 lines to the variables…
SELECT @v_Now = GETDATE()
,@v_DB = DB_NAME()
,@v_Obj = '[DBCATCH].[dbo].[PU_Demo1]'
,@v_App = APP_NAME()
,@v_User = ISNULL(ORIGINAL_LOGIN(), USER_NAME())
,@v_Spid = CONVERT([NVARCHAR](25), @@SPID)
,@v_System = HOST_NAME()
,@v_Debug = 0;
We defined the variable @v_obj when we set the parameters above. The system functions define the rest of the variables. If you look closely, they may look familiar. Remember the table TU_Errorlog from above? Here’s the table definition again:
CREATE TABLE [dbo].[TU_ErrorLog]
(
[ErrorLog_ID] [INT] IDENTITY (1, 1) NOT NULL
CONSTRAINT [PK_ErrorLog_ID]
PRIMARY KEY CLUSTERED
,[Error_ID] [INT] NOT NULL
CONSTRAINT [FK_TU_ErrorLog_Error_ID]
FOREIGN KEY ([Error_ID])
REFERENCES [TU_Error]([Error_ID])
,[System] [NVARCHAR](100) NULL
,[DB] [NVARCHAR](100) NULL
,[Obj] [NVARCHAR](100) NULL
,[App] [NVARCHAR](100) NULL
,[USER] [NVARCHAR](100) NULL
,[SPID] [NVARCHAR](100) NULL
,[ErrorMsg] [NVARCHAR](2500) NULL
,[Active] [BIT] NOT NULL
CONSTRAINT [DF_TU_ErrorLog_Active]
DEFAULT 1
,[DateCreated] [DATETIME] NOT NULL
CONSTRAINT [DF_TU_ErrorLog_DateCreated]
DEFAULT GETDATE()
,[DateModified] [DATETIME] NOT NULL
CONSTRAINT [DF_TU_ErrorLog_DateModified]
DEFAULT GETDATE()
) ON [PRIMARY];
GO
Do some of the variables look familiar? We are not at that “a-ha!” moment yet, but I am hoping you are connecting the dots. These variables are being defined dynamically at run time that will be used to populate the error log table when we call the TU_ErrorLog_ADD Procedure.
The last part of the variables, defined above, which needs your attention is the @v_Debug value that is set to 0. When developing your stored procedure you may want to set this to 1 and use this technique throughout your logic. Copy and paste the if statement and supply your own run time variables you will be using as shown below:
IF @v_Debug = 1
BEGIN -- debug = 1 Ref: 1
SELECT 'REF:1 - Define Static Variables';
SELECT @v_Now AS [GETDATE]
,@v_DB AS [DB]
,@v_Obj AS [OBJ]
,@v_App AS [APP]
,@v_User AS [USER]
,@v_Spid AS [SPID];
END; -- debug = 1
Kicking the Tires
Now that the variables are set, we go to the Begin Try / End Try part of the template where we see a comment: Start Code Here! I bet you cannot guess where you start your logic code of the stored procedure… Yeah, it really is that simple. When you are done with your stored procedure and it is error-free, then you are done. That’s it.
BEGIN TRY
--==>> Start Code Here!
END TRY
If by chance you do get an error, then we go into the error trapping and error message customization part of the template called the begin catch and the end catch. (We love the try and catch functionality in stored procedures. Where do you think the name DBCatch comes from?)
BEGIN CATCH
SELECT @v_Error = 100000
,@v_MSG = CONVERT(NVARCHAR(2500),
'Error: ' + CONVERT([NVARCHAR](255), ISNULL(ERROR_NUMBER(), -1)) +
' Severity: ' + CONVERT([NVARCHAR](255), ISNULL(ERROR_SEVERITY(), -1)) +
' State: ' + CONVERT([NVARCHAR](255), ISNULL(ERROR_STATE(), -1)) +
' Line: ' + CONVERT([NVARCHAR](255), ISNULL(ERROR_LINE(), -1)) +
' Procedure: ' + CONVERT([NVARCHAR](255), ISNULL(ERROR_PROCEDURE(), @v_Obj)) +
' MSG: Error from [DBCATCH].[dbo].[TU_Errorlog] with error:' + ISNULL(ERROR_MESSAGE(), ''));
EXEC [DBCATCH].[dbo].[PU_ErrorLog_ADD]
@p_Error_ID = @v_Error,
@p_System = @v_System,
@p_DB = @v_DB,
@p_Obj = @v_Obj,
@p_App = @v_App,
@p_User = @v_User,
@p_ErrorMsg = @v_MSG;
SELECT @v_Msg;
RETURN @v_Error;
END CATCH;
Once we capture the error, we take our defined variables from above and concatenate a run time message that should give us some insight about the object in question. Using some system error functions we create a meaningful message that should help us determine what’s going on.
It then takes the information and calls the error log stored proc, which adds the record to the table. Then it prints the message just in case you are calling it from management studio. It may look like a lot, but once you break it down into its individual pieces it is really pretty easy to consume. Let’s finish this segment by creating a simple demo that will error out and we can see our framework in action.
Demo1
First, we take our template and hold the keys SHIFT/CTRL/M and name the stored procedure PU_Demo1.
Second we go to the line where it says start code here:
–==>> Start Code Here!
And we replace it with:
SELECT 'This is Demo1'
RAISERROR ('Message', 19, 1) WITH LOG
Third, we compile the stored procedure by clicking on the execute button, and then we are run the procedure by opening a new query window and typing:
USE DBCatch;
GO
EXEC PU_Demo1;
GO
Once we execute that we will see the following message below:
Lastly, if we go to the error log table we should see a single record like that below:
Conclusion
We really did not get some earth-shattering enlightenment about the error with our SQL error engine. But lets face it, all we did is raiserror with nothing going on so there was not much to go on.
But, we do have the information about where the error happened (TU_Demo1), from where (management studio) and from what system. You could say this Demo was the worst-case solution in terms of getting a great message out of the error (I wanted to keep the demo as simple as we could and show the worst case). This at least gives us a pretty good starting point. Especially if your solution had 100 stored procs wrapped in its process running in a job every night. I hope you are thinking where you can start using this functionality as we come to a close of the second part of this series.
Come back when we extend the functionality of this framework by adding a running activity log that will interact with our already running error handler.
