Building a T-SQL Framework

Depending on the objective, building a framework allows the architect to imbed the fundamental segments of a structure so that the existing requirements can be built on top of the foundation, leveraging the functionality of the framework. This usually includes core functionality that repeats consistently through a process like Logging, Error handling, Successes/Failure Notifications, Import/Export Validations, and File System  processes  just to name a few.

There are several different types of frameworks. ETL Frameworks have been receiving the most popularity lately, but they can extend to T-SQL, Analysis Services, Reporting Services, CLR, and even PowerShell within Microsoft SQL Server Data Services. Usually all frameworks will have a simple set of core processes that would benefit any new process by embedding the procedure in question to run consistently through a mechanism such as a template that would allow the extension of logic code from developers.

Today we are going to start a multi-step project to create a basic T-SQL framework from the ground up. This framework will have three components, including:

  • Logging – Each process that runs in the framework will log its activity to a table. This feature can also be leveraged later if you created an ETL framework or extended to add additional information according to your business needs.
  • Batching – Each T-sql procedure will be grouped into a batch. This is another extension of logging but it is also the the same foundation in which to build batching row sets for insert, update, delete, or even selects.
  • Error Handling – The framework will incorporate some custom error handling that will identify, define, and log errors that happen through the framework.

In the end we will create a stored procedure template that will facilitate the consistent execution of the frameworks features while allowing the developer to code the logic necessary for their task at hand.

In this section of the project we are going to look at creating a new database, the table structures necessary to support the framework, and populate the database with base data (insert into the code tables), that will allow our framework to be data driven.

The first thing we are going to do is create a database called DBCatch.  The defaults will be fine for the database with the statement below:

USE master;
GO
 
 
IF NOT EXISTS(SELECT 1
                FROM master.sys.databases
	        WHERE name = 'DBCatch')
BEGIN
 
  CREATE DATABASE DBCatch;
 
END;
GO

Now let’s take a quick look at the tables that we will create and give an explanation of each one. This is a great place to start a data dictionary but since that is outside of our scope we will not be defining that today. Here is a look at the database diagram once we have all our tables built:

T-sql framework diagram

First, start with the code tables CodeGroup and Code. We do not want to clutter our database with all the code tables that will be needed to make the database data driven so we combine them into a name value pair. Let’s take a phone code example for instance. If we were storing phone numbers, then the overall group name would be phone so we would make an entry into the CodeGroup called phone. Next, we take the primary key that was created for the group phone and we carry it over to the Code table when we define what kind of phone entry we are inserting like:

  • Cell Phone
  • Home Phone
  • Work Phone
  • Fax
  • Toll Free Phone
  • Alternate Phone

This type of code works for 90% of the codes and definitions we want, but we need another structure for everything else. We will use a technique called the registry table. Back in the day, some developers used the windows registry to store look up data but that became problematic. Great idea but the implementation left something to be desired, so a new idea of creating a structure/table like the registry was created and adopted by a few developers and by us today. The registry table is made up of a RegKey (Registry Key),  like for example if you wanted an email list we could have a RegKey of:

HKEY_LOCAL_MACHINE\SOFTWARE\DBCatch\MAINT\DBMAIL\RECIPIENTS

And then the RegValue (Registry Value) of the emails that make up the email list. Now, one last thing that is important about this table is that it is not encrypted because everyone has their own favorite flavor of encryption, and the nature of this type of data is usually somewhat sensitive (Data paths, email addresses, Powershell directories, etc.). We create a simple level of obscurity by converting the character values into varbinary and then back to character when we need to use them. This is not a bad thing because we will design the framework to do so seamlessly.

The first feature set of tables are the error handling tables. We would normally start with our number one feature we defined (logging), but it is dependent on the error handling functionality in order to be complete. The error tables are Error which will define the error, and ErrorLog which will hold a record for each error that is thrown. In the Error table we will first have to go to our code tables and create a group (CodeGroup), called “Error”, and then define the codes (Code), of the error that will be caught such as:

  • Default or Unknown
  • SQL – Internal sql server error
  • DBCATCH generated error
  • INSERT
  • UPDATE
  • DELETE
  • T-SQL (usually for dynamic sql)
  • ESCALATION – error that has been escalated to a parent error
  • MAINTENACE – a maintenance related error

The primary key for the Code table will be set in the Foreign Key column “Type_ID” that describes the type of error we are defining. The additional attributes Name, Severity, State, and Desc will be described in detail in the next segment when we define the logic code for the Error and ErrorLog tables.

The next table is the Activity log or ActivityLog.  It will hold a record for every procedure that is executed within the framework, and if there is an error with the procedure it will show that here as well. The activity log will log the who, what, wherefrom, when, and how long did it take. This information will be obtained at runtime through the template.

The last set of records are the “Batch” and “BatchDetail” tables that will extend the logging into groups so that we can see where in a process things go wrong, slow down, or just what groups of statements are being executed together. Although we are only dealing with stored procedure batches at the moment, we are taking into account that we will want to extend the functionality to batch records, or even processes at a future date so we will populate a type_id foreign key that reflects the code for stored procedure batch. The rest of the attributes will be defined in later segments when the logic code is created and the template is designed to wrap the functionality up into a working framework.

Now that we have a brief explanation of the tables we are going to provide the DDL to create the tables in the T-SQL Statement below.

/************************************************************************/
/* Name	        : Create DBCatch Table Structures                       */
/* Version      : 1.0                                                   */
/* Author       : Jared Kirkpatrick                                     */
/* Date	        : 2015-11-15                                            */
/* Description	: Create Table Structures                               */
/************************************************************************/
/* Date         : Version: Who: Description                             */
/************************************************************************/
/* 2015-11-15   : 1.0    : JSK: Initial Release.                        */
/************************************************************************/
 
 
/************************************************************************/
/* ::NOTES::														    */
/************************************************************************/
 
 
USE DBCatch;
GO
 
DECLARE @v_Mode INT
 
SELECT @v_Mode = 1 -- 0 = drop only 1 = drop/create 2 = create only
 
IF @v_Mode < 2
BEGIN
 
 
/*****************************BASE FRAMEWORK*********************************/
IF OBJECT_ID(N'[dbo].[ErrorLog]', 'u') IS NOT NULL 
BEGIN
PRINT CHAR(10) + CHAR(13) + 'Dropping table [dbo].[ErrorLog].';	
DROP TABLE [dbo].[ErrorLog];
END;
 
/*****************************BASE FRAMEWORK*********************************/
IF OBJECT_ID(N'[dbo].[ActivityLog]', 'u') IS NOT NULL 
BEGIN
PRINT CHAR(10) + CHAR(13) + 'Dropping table [dbo].[ActivityLog].';	
DROP TABLE [dbo].[ActivityLog];
END;
 
/*****************************BASE FRAMEWORK*********************************/
IF OBJECT_ID(N'[dbo].[BatchDetail]', 'u') IS NOT NULL 
BEGIN
PRINT CHAR(10) + CHAR(13) + 'Dropping table [dbo].[BatchDetail].';	
DROP TABLE [dbo].[BatchDetail];
END;
 
/*****************************BASE FRAMEWORK*********************************/
IF OBJECT_ID(N'[dbo].[Batch]', 'u') IS NOT NULL 
BEGIN
PRINT CHAR(10) + CHAR(13) + 'Dropping table [dbo].[Batch].';	
DROP TABLE [dbo].[Batch];
END;
 
/*****************************BASE FRAMEWORK*********************************/
IF OBJECT_ID(N'[dbo].[Error]', 'u') IS NOT NULL 
BEGIN
PRINT CHAR(10) + CHAR(13) + 'Dropping table [dbo].[Error].';	
DROP TABLE [dbo].[Error];
END;
 
/*****************************BASE FRAMEWORK*********************************/	
IF OBJECT_ID(N'[dbo].[Registry]', 'u') IS NOT NULL
BEGIN
PRINT CHAR(10) + CHAR(13) + 'Dropping table [dbo].[Registry].';	
DROP TABLE [dbo].[Registry];
END;	
 
/*****************************BASE FRAMEWORK*********************************/
IF OBJECT_ID(N'[dbo].[Code]', 'u') IS NOT NULL 
BEGIN
PRINT CHAR(10) + CHAR(13) + 'Dropping table [dbo].[Code].';	
DROP TABLE [dbo].[Code];
END;
 
/*****************************BASE FRAMEWORK*********************************/
IF OBJECT_ID(N'[dbo].[CodeGroup]', 'u') IS NOT NULL 
BEGIN
PRINT CHAR(10) + CHAR(13) + 'Dropping table [dbo].[CodeGroup].';	
DROP TABLE [dbo].[CodeGroup];
END;
 
END;	
 
 
IF @v_Mode >= 1
BEGIN   -- @v_Mode >= 1
 
/************************************************************************/
/* ::NOTES:  Create the objects                                        	*/
/************************************************************************/
 
/*================================================================================================*/
/****** Object:  Table [dbo].[CodeGroup]          Script Date: 2015-11-15                 ******/
/*================================================================================================*/
 
PRINT CHAR(10) + CHAR(13) + 'Creating table [dbo].[CodeGroup].';	
 
CREATE TABLE [dbo].[CodeGroup]
(
[CodeGroup_ID]               [INT]  IDENTITY (1, 1)                                        NOT NULL
CONSTRAINT [PK_CodeGroup_ID]   
PRIMARY KEY CLUSTERED
,[Name]                       [NVARCHAR](100) COLLATE SQL_Latin1_General_CP1_CI_AS          NOT NULL
,[DESC]                       [NVARCHAR](255) COLLATE SQL_Latin1_General_CP1_CI_AS          NOT NULL
CONSTRAINT DF_CodeGroup_Desc
DEFAULT ('Group Code')
,[Active]                     [BIT]                                                         NOT NULL
CONSTRAINT DF_CodeGroup_Active
DEFAULT ((1))
,[DateCreated]                [DATETIME]                                                    NOT NULL
CONSTRAINT DF_CodeGroup_DateCreated
DEFAULT (GETDATE())
,[DateModified]               [DATETIME]                                                    NOT NULL
CONSTRAINT DF_CodeGroup_DateModified
DEFAULT (GETDATE())
) ON [PRIMARY];
 
/* Alt key Unique index */
CREATE UNIQUE NONCLUSTERED INDEX ak_CodeGroup_Name ON [dbo].[CodeGroup]([Name])
 
 
/*================================================================================================*/
/****** Object:  Table [dbo].[Code]            Script Date: 2015-11-15                    ******/
/*================================================================================================*/
 
PRINT CHAR(10) + CHAR(13) + 'Creating table [dbo].[Code].';	
 
 
CREATE TABLE [dbo].[Code]
(
[Code_ID]                    [INT]  IDENTITY (1, 1)                                        NOT NULL 
CONSTRAINT [PK_Code_ID]   
PRIMARY KEY CLUSTERED
,[CodeGroup_ID]               [INT]                                                         NOT NULL
CONSTRAINT [FK_Code_CodeGroup_ID]
FOREIGN KEY ([CodeGroup_ID]) 
REFERENCES [dbo].[CodeGroup]([CodeGroup_ID])
,[Name]                       [NVARCHAR](100) COLLATE SQL_Latin1_General_CP1_CI_AS          NOT NULL
,[DESC]                       [NVARCHAR](255) COLLATE SQL_Latin1_General_CP1_CI_AS          NOT NULL
CONSTRAINT [DF_Code_Desc]
DEFAULT 'Code'
,[Active]                     [BIT]                                                         NOT NULL
CONSTRAINT [DF_Code_Active]
DEFAULT 1
,[DateCreated]                [DATETIME]                                                    NOT NULL
CONSTRAINT [DF_Code_DateCreated]
DEFAULT GETDATE()
,[DateModified]               [DATETIME]                                                    NOT NULL
CONSTRAINT [DF_Code_DateModified]
DEFAULT GETDATE()
) ON [PRIMARY];
 
 
/* FK Index */
CREATE NONCLUSTERED INDEX idxfk_Code_CodeGroup_ID ON [dbo].[Code]([CodeGroup_ID])
 
 
/*================================================================================================*/
/****** Object:  Table [dbo].[Registry]        Script Date: 2015-11-15                    ******/
/*================================================================================================*/
IF OBJECT_ID(N'[dbo].[Registry]', 'u') IS NULL
BEGIN
 
PRINT CHAR(10) + CHAR(13) + 'Creating table [dbo].[Registry].';	
 
CREATE TABLE [dbo].[Registry]
(
[Registry_ID]			       [INT]  IDENTITY (1,1)                                         NOT NULL 
CONSTRAINT [PK_Registry_ID]   
PRIMARY KEY CLUSTERED
,[RegKey]				       [NVARCHAR](255) COLLATE SQL_Latin1_General_CP1_CI_AS	         NOT NULL
,[RegValue]				   [VARBINARY](255)                                              NOT NULL
,[Active]                     [BIT]                                                         NOT NULL
CONSTRAINT [DF_Registry_Active]
DEFAULT (1)
,[DateCreated]			       [DATETIME]      					                             NOT NULL
CONSTRAINT [DF_Registry_DateCreated]
DEFAULT GETDATE()
,[DateModified]			   [DATETIME]												     NOT NULL
CONSTRAINT [DF_Registry_DateModified]
DEFAULT GETDATE()
) ON [PRIMARY];
 
END;
 
 
/* Alt key Unique index */
CREATE UNIQUE NONCLUSTERED INDEX ak_Registry_RegKey ON [dbo].[Registry]([RegKey]) INCLUDE ([RegValue])
 
/*================================================================================================*/
/****** Object:  Table [dbo].[Error]           Script Date: 2015-11-15                    ******/
/*================================================================================================*/
 
PRINT CHAR(10) + CHAR(13) + 'Creating table [dbo].[Error].';	
 
 
CREATE TABLE [dbo].[Error]
(
[Error_ID]                   [INT]  IDENTITY (1, 1)                                        NOT NULL 
CONSTRAINT [PK_Error_ID]   
PRIMARY KEY CLUSTERED
,[Type_ID]                    [INT]                                                         NOT NULL
CONSTRAINT [FK_Error_Code_ID]
FOREIGN KEY ([Type_ID]) 
REFERENCES [dbo].[Code]([Code_ID])
,[Name]                       [NVARCHAR](100) COLLATE SQL_Latin1_General_CP1_CI_AS          NOT NULL
,[Severity]                   [INT]                                                         NULL
,[STATE]                      [INT]                                                         NULL
,[DESC]                       [NVARCHAR](255) COLLATE SQL_Latin1_General_CP1_CI_AS          NOT NULL
CONSTRAINT [DF_Error_Desc]
DEFAULT 'Error'
,[Active]                     [BIT]                                                         NOT NULL
CONSTRAINT [DF_Error_Active]
DEFAULT 1
,[DateCreated]                [DATETIME]                                                    NOT NULL
CONSTRAINT [DF_Error_DateCreated]
DEFAULT GETDATE()
,[DateModified]               [DATETIME]                                                    NOT NULL
CONSTRAINT [DF_Error_DateModified]
DEFAULT GETDATE()
) ON [PRIMARY];
 
 
/* FK Index */
CREATE NONCLUSTERED INDEX idxfk_Error_Type_ID ON [dbo].[Error]([Type_ID])
 
 
/*================================================================================================*/
/****** Object:  Table [dbo].[Batch]           Script Date: 2015-11-15                    ******/
/*================================================================================================*/
 
PRINT CHAR(10) + CHAR(13) + 'Creating table [dbo].[Batch].';	
 
 
CREATE TABLE [dbo].[Batch]
(
[Batch_ID]			       [UNIQUEIDENTIFIER]                                            NOT NULL
CONSTRAINT [PK_Batch_ID]   
PRIMARY KEY CLUSTERED
CONSTRAINT [DF_Batch_ID]
DEFAULT CONVERT(UNIQUEIDENTIFIER, CONVERT(BINARY(10), NEWID()) + 
CONVERT(BINARY(6), GETDATE())) 
,[Type_ID]                    [INT]                                                         NOT NULL
CONSTRAINT [FK_Batch_Type_ID]
FOREIGN KEY ([Type_ID]) 
REFERENCES [dbo].[Code]([Code_ID]) 
CONSTRAINT [DF_Batch_Type_ID]
DEFAULT 0    
,[Name]                       [NVARCHAR](100) COLLATE SQL_Latin1_General_CP1_CI_AS          NOT NULL
CONSTRAINT [DF_Batch_Name]
DEFAULT 'Batch:' + CONVERT(NVARCHAR(10), GETDATE(), 110)    
,[Parent_ID]                  [INT]                                                         NULL
,[Active]                     [BIT]                                                         NOT NULL
CONSTRAINT [DF_Batch_Active]
DEFAULT 1
,[DateCreated]                [DATETIME]                                                    NOT NULL
CONSTRAINT [DF_Batch_DateCreated]
DEFAULT GETDATE()
,[DateModified]               [DATETIME]                                                    NOT NULL
CONSTRAINT [DF_Batch_DateModified]
DEFAULT GETDATE()
) ON [PRIMARY];
 
 
/* FK Index */
CREATE NONCLUSTERED INDEX idxfk_Batch_Type_ID ON [dbo].[Batch]([Type_ID])
 
 
/*================================================================================================*/
/****** Object:  Table [dbo].[BatchDetail]        Script Date: 2015-11-15                 ******/
/*================================================================================================*/
 
PRINT CHAR(10) + CHAR(13) + 'Creating table [dbo].[BatchDetail].';	
 
 
CREATE TABLE [dbo].[BatchDetail]
(
[BatchDetail_ID]			   [BIGINT]  IDENTITY (1, 1)                                     NOT NULL 
CONSTRAINT [PK_BatchDetail_ID]   
PRIMARY KEY CLUSTERED
,[Batch_ID]                   [UNIQUEIDENTIFIER]                                            NOT NULL
CONSTRAINT [FK_BatchDetail_Batch_ID]
FOREIGN KEY ([Batch_ID]) 
REFERENCES [dbo].[Batch]([Batch_ID]) 
,[BatchSize]                  [INT]                                                         NOT NULL
CONSTRAINT [DF_Batch_BatchSize]
DEFAULT 0     
,[OBJECT_ID]                  [INT]                                                         NULL   
,[Entity_ID]                  [UNIQUEIDENTIFIER]                                            NULL   
,[OBJECT]                     [NVARCHAR](100) COLLATE SQL_Latin1_General_CP1_CI_AS          NULL
,[StartDate]                  [DATETIME]                                                    NULL
,[EndDate]                    [DATETIME]                                                    NULL
,[Rank]                       [INT]                                                         NOT NULL
CONSTRAINT [DF_BatchDetail_Rank]
DEFAULT 1
,[Active]                     [BIT]                                                         NOT NULL
CONSTRAINT [DF_BatchDetail_Active]
DEFAULT 1
,[DateCreated]                [DATETIME]                                                    NOT NULL
CONSTRAINT [DF_BatchDetail_DateCreated]
DEFAULT GETDATE()
,[DateModified]               [DATETIME]                                                    NOT NULL
CONSTRAINT [DF_BatchDetail_DateModified]
DEFAULT GETDATE()
) ON [PRIMARY];
 
 
/* FK Index */
CREATE NONCLUSTERED INDEX idxfk_BatchDetail_Batch_ID ON [dbo].[BatchDetail]([Batch_ID])
 
/*================================================================================================*/
/****** Object:  Table [dbo].[ActivityLog]         Script Date: 2015-11-15                ******/
/*================================================================================================*/
PRINT CHAR(10) + CHAR(13) + 'Creating table [dbo].[ActivityLog].';	
 
CREATE TABLE [dbo].[ActivityLog] 
(
[ActivityLog_ID]             [BIGINT]  IDENTITY (1, 1)                                     NOT NULL
CONSTRAINT [PK_ActivityLog_ID]   
PRIMARY KEY CLUSTERED  
,[Error_ID]                   [INT]                                                         NULL
CONSTRAINT [FK_ActivityLog_Error_ID]
FOREIGN KEY ([Error_ID]) 
REFERENCES [dbo].[Error]([Error_ID])   
,[SystemName]                 [NVARCHAR](100) COLLATE SQL_Latin1_General_CP1_CI_AS          NULL
,[DB]                         [NVARCHAR](100) COLLATE SQL_Latin1_General_CP1_CI_AS          NULL
,[Obj]                        [NVARCHAR](100) COLLATE SQL_Latin1_General_CP1_CI_AS          NULL
,[App]                        [NVARCHAR](100) COLLATE SQL_Latin1_General_CP1_CI_AS          NULL
,[USER]                       [NVARCHAR](100) COLLATE SQL_Latin1_General_CP1_CI_AS          NULL 
,[SPID]                       [NVARCHAR](100) COLLATE SQL_Latin1_General_CP1_CI_AS          NULL 
,[Description]                [NVARCHAR](512)	COLLATE SQL_Latin1_General_CP1_CI_AS         NULL 
,[StartTime]                  [DATETIME]                                                    NULL
,[EndTime]                    [DATETIME]                                                    NULL 
,[Active]                     [BIT]                                                         NOT NULL
CONSTRAINT [DF_ActivityLog_Active]
DEFAULT (1)
,[DateCreated]			       [DATETIME]												     NOT NULL
CONSTRAINT [DF_ActivityLog_DateCreated]
DEFAULT GETDATE()
,[DateModified]			   [DATETIME]												     NOT NULL
CONSTRAINT [DF_ActivityLog_DateModified]
DEFAULT GETDATE()			
) ON [PRIMARY];
 
 
/* FK Index */
CREATE NONCLUSTERED INDEX idxfk_ActivityLog_Error_ID ON [dbo].[ActivityLog]([Error_ID])
 
/*================================================================================================*/
/****** Object:  Table [dbo].[ErrorLog]           Script Date: 2015-11-15                 ******/
/*================================================================================================*/
 
PRINT CHAR(10) + CHAR(13) + 'Creating table [dbo].[ErrorLog].';	
 
 
CREATE TABLE [dbo].[ErrorLog]
(
[ErrorLog_ID]                [INT]  IDENTITY (1, 1)                                        NOT NULL 
CONSTRAINT [PK_ErrorLog_ID]   
PRIMARY KEY CLUSTERED
,[Error_ID]                   [INT]                                                         NOT NULL
CONSTRAINT [FK_ErrorLog_Error_ID]
FOREIGN KEY ([Error_ID]) 
REFERENCES [dbo].[Error]([Error_ID])   
,[DB]                         [NVARCHAR](100) COLLATE SQL_Latin1_General_CP1_CI_AS          NULL
,[Obj]                        [NVARCHAR](100) COLLATE SQL_Latin1_General_CP1_CI_AS          NULL
,[App]                        [NVARCHAR](100) COLLATE SQL_Latin1_General_CP1_CI_AS          NULL
,[USER]                       [NVARCHAR](100) COLLATE SQL_Latin1_General_CP1_CI_AS          NULL
,[SPID]                       [NVARCHAR](100) COLLATE SQL_Latin1_General_CP1_CI_AS          NULL
,[ErrorMsg]                   [NVARCHAR](2500)COLLATE SQL_Latin1_General_CP1_CI_AS          NULL 
,[Active]                     [BIT]                                                         NOT NULL
CONSTRAINT [DF_ErrorLog_Active]
DEFAULT 1
,[DateCreated]                [DATETIME]                                                    NOT NULL
CONSTRAINT [DF_ErrorLog_DateCreated]
DEFAULT GETDATE()
,[DateModified]               [DATETIME]                                                    NOT NULL
CONSTRAINT [DF_ErrorLog_DateModified]
DEFAULT GETDATE()
) ON [PRIMARY];
 
 
/* FK Index */
CREATE NONCLUSTERED INDEX idxfk_ErrorLog_Error_ID ON [dbo].[ErrorLog]([Error_ID])
 
/*================================================================================================*/
/****** Object:  Table [dbo].[Application]        Script Date: 2015-11-15                 ******/
/*================================================================================================*/
 
PRINT CHAR(10) + CHAR(13) + 'Creating table [dbo].[Application].';	
 
 
CREATE TABLE [dbo].[Application]
(
[Application_ID]             [INT]  IDENTITY (1, 1)                                        NOT NULL 
CONSTRAINT [PK_Application_ID]   
PRIMARY KEY CLUSTERED
,[ApplicationName]            [NVARCHAR](255) COLLATE SQL_Latin1_General_CP1_CI_AS          NOT NULL
CONSTRAINT U_Applications_ApplicationName
UNIQUE
,[AppDesc]                    [NVARCHAR](512) COLLATE SQL_Latin1_General_CP1_CI_AS          NULL
,[Active]                     [BIT]                                                         NOT NULL
CONSTRAINT [DF_Application_Active]
DEFAULT 1
,[DateCreated]                [DATETIME]                                                    NOT NULL
CONSTRAINT [DF_Application_DateCreated]
DEFAULT GETDATE()
,[DateModified]               [DATETIME]                                                    NOT NULL
CONSTRAINT [DF_Application_DateModified]
DEFAULT GETDATE()
) ON [PRIMARY];
 
 
 
 
END
GO

The last part of this segment is to populate the data-driven tables which are CodeGroup, Code, Registry, and Error. Now we are populating Error so that we can capture errors and put them in one of the groups we are defining. The other thing we are doing is creating new errors in SQL server but we are not completely defining them, what I mean is that the error message that is usually associate with an error is left out by design so that the message can be created at runtime for a more user friendly surgical message. This will be explained in much more detail in the last segment but take a look at the code because it will be referenced in the near future. The code is listed below but one last thing is that you must have elevated privileges (sa sysadmin) to run this script since it does add system level functionality:

USE [DBCatch];
GO
 
SET NOCOUNT ON;
 
DECLARE @v_Debug               [BIT]                          = 1 -- 1 = print all info msgs, 0 silent install
,@v_Error               [INT]                          = 0
,@v_NewLine             [NCHAR](2)                     = CHAR(13) + CHAR(10)
,@v_DynSQL2             [NCHAR](1)                     = CHAR(39)
,@v_MSG                 [NVARCHAR](2000)
,@v_SQL                 [NVARCHAR](4000);
 
 
/* Registry */
IF @v_Debug = 1 
BEGIN -- debug code
 
SELECT 'Populating Table: [DBCatch].[dbo].[Registry]';
 
END; -- debug code
 
/* Registry */
INSERT [DBCatch].[dbo].[Registry]
(RegKey, RegValue)
SELECT 'HKEY_LOCAL_MACHINE\SOFTWARE\DBCatch\MAINTVER', CONVERT(VARBINARY(255), '0.01')
UNION
SELECT 'HKEY_LOCAL_MACHINE\SOFTWARE\DBCatch\MAINT\DBMAIL\RECIPIENTS', CONVERT(VARBINARY(255), 'jared.kirpatric@sqlsolutionsgroup.com;')
UNION
SELECT 'HKEY_LOCAL_MACHINE\SOFTWARE\DBCatch\PSS\PATH', CONVERT(VARBINARY(255), 'C:\MSSQL\Code\DBCatch\PSScripts\')
UNION
SELECT 'HKEY_LOCAL_MACHINE\SOFTWARE\DBCatch\SSIS\PATH', CONVERT(VARBINARY(255), 'C:\MSSQL\Code\DBCatch\Projects\SSISConfig\')
UNION
SELECT 'HKEY_LOCAL_MACHINE\SOFTWARE\DBCatch\BATCH\ROWSET', CONVERT(VARBINARY(255), '25000')
UNION
SELECT 'HKEY_LOCAL_MACHINE\SOFTWARE\CLETL\EXTRACTHIST\SEC', CONVERT(VARBINARY(255), '300')
 
 
 
IF @v_Debug = 1 
BEGIN -- debug code
 
SELECT '[DBCatch].[dbo].[Registry] VALUES';
 
SELECT CONVERT([VARCHAR](2000), [RegValue])
,* 
FROM [DBCatch].[dbo].[Registry] (NOLOCK);
 
-- select dbo.FSU_RegValue_GET ('HKEY_LOCAL_MACHINE\SOFTWARE\DBCatch\EXTRACTHIST\SEC')
 
END; -- debug code 
 
/* Codes */
IF @v_Debug = 1 
BEGIN -- debug code
 
SELECT 'Populating TABLE: [DBCatch].[dbo].[CodeGroup]';
 
END; -- debug code
 
SET IDENTITY_INSERT [dbo].[CodeGroup] ON;
 
INSERT [DBCatch].[dbo].[CodeGroup]
(      CodeGroup_ID, Name,                    [Desc])
SELECT 0,            'DEFAULT',               'TEMP/UNKNOWN CODE GROUP'
UNION
SELECT 1,            'ERROR',                 'ERROR CODES'
UNION
SELECT 2,            'BATCH',                 'TYPE OF BATCHS'
UNION
SELECT 4,            'LINEAGE',               'TYPE OF LINEAGE';
 
SET IDENTITY_INSERT [dbo].[CodeGroup] OFF;
 
 
IF @v_Debug = 1 
BEGIN -- debug code
 
SELECT '[DBCatch].[dbo].[CodeGroup] VALUES';
 
SELECT * 
FROM [DBCatch].[dbo].[CodeGroup] (NOLOCK);
 
END; -- debug code
 
IF @v_Debug = 1 
BEGIN -- debug code
 
SELECT 'Populating TABLE: [DBCatch].[dbo].[Code]';
 
END; -- debug code
 
SET IDENTITY_INSERT [dbo].[Code] ON;
 
INSERT [DBCatch].[dbo].[Code]
(      Code_ID, CodeGroup_ID, Name,                    [Desc])
SELECT 0,       0,            'DEFAULT',               'TEMP/UNKNOWN CODE'
UNION
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,            'INSERT',                'SQL TABLE INSERT RELATED ERROR'
UNION
SELECT 104,     1,            'UPDATE',                'SQL TABLE UPDATE RELATED ERROR'
UNION
SELECT 105,     1,            'DELETE',                'SQL TABLE DELETE RELATED ERROR'
UNION
SELECT 106,     1,            'T-SQL',                 'T-SQL RELATED ERROR'
UNION 
SELECT 107,     1,            'ESCLATION',             'ERROR THAT HAS BEEN ESCALATED TO A PARENT ERROR'
UNION   
SELECT 108,     1,            'MAINTENANCE',           'DATABASE MAINTENANCE RELATED ERROR'
UNION
SELECT 200,     2,            'DEFAULT',               'DEFAULT BATCH TYPE'
UNION
SELECT 201,     2,            'PROCEDURE',             'SP BATCH FOR ORDINAL COLLECTION'
UNION
SELECT 202,     2,            'RECORDCOUNT',           'SET NUMBER OF RECORDS TO BE BATCHED'
UNION
SELECT 400,     4,            'DEFAULT',               'DEFAULT LINEAGE'
UNION
SELECT 401,     4,            'TABLE',                 'SOURCE TABLE LINEAGE';
 
 
SET IDENTITY_INSERT [dbo].[Code] OFF;
 
IF @v_Debug = 1 
BEGIN -- debug code
 
SELECT '[DBCatch].[dbo].[Code] VALUES';
 
SELECT * 
FROM [DBCatch].[dbo].[Code] (NOLOCK);
 
END; -- debug code
 
IF @v_Debug = 1 
BEGIN -- debug code
 
SELECT 'Populating TABLE: [DBCatch].[dbo].[Error]';
 
END; -- debug code
 
SET IDENTITY_INSERT [dbo].[Error] ON;
 
INSERT [DBCatch].[dbo].[Error]
(      Error_ID, [Type_ID], [Name],              [Severity],[State], [Desc])
SELECT 0,        100,       'DEFAULT',           '16',      '1',     'UNKNOWN/TEMP ERROR'
UNION
SELECT 100000,   102,       'DEFAULT_DBCatch',   '16',      '1',     'UNKNOWN/TEMP ERROR IN DBCatch'
UNION  
SELECT 100001,   102,       'DBCatch_INSERT',    '16',      '1',     'ERROR INSERTING INTO A TABLE'
UNION  
SELECT 100002,   102,       'DBCatch_UPDATE',    '16',      '1',     'ERROR UPDATING A TABLE'
UNION  
SELECT 100003,   102,       'DBCatch_DELETE',    '16',      '1',     'ERROR DELETING FROM A TABLE'
UNION  
SELECT 100004,   102,       'DBCatch_SELECT',    '16',      '1',     'ERROR DBCatch DATA COLLECTOR (SELECTING INFO)'
UNION  
SELECT 100005,   102,       'DBCatch_ASSEMBLY',  '16',      '1',     'ERROR DBCatch C# SP/FN'
UNION
SELECT 100006,   102,       'DBCatch_WMIC',      '16',      '1',     'ERROR DBCatch WMIC Query'
UNION  
SELECT 100007,   102,       'ERRORENGINE',       '16',       '1',    'ERROR WITHIN THE FOUNDATION'
UNION  
SELECT 100008,   102,       'BACKUP',            '16',       '1',     'ERROR WITH A BACKUP'
UNION  
SELECT 100009,   102,       'SQLJOB',            '16',       '1',     'ERROR WITH A SQL JOB'
UNION  
SELECT 100010,   104,       'REINDEX',           '16',       '1',     'ERROR EXECUTING DATABASE REINDEXING MAINTENANCE UTILITY';
 
SET IDENTITY_INSERT [dbo].[Error] OFF;
 
IF @v_Debug = 1 
BEGIN -- debug code
 
SELECT '[DBCatch].[dbo].[Error] VALUES';
 
SELECT * 
FROM [DBCatch].[dbo].[Error] (NOLOCK);
 
END; -- debug code
 
/* Add error Messages */
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';

We are well on our way to having a basic T-SQL framework that is scalable enough when completed that it can be extended itself or incorporated into a new framework outside of just stored procedures.

Please share this
Tags
#microsftcertifedmaster #microsftpartner #parallelism #azure #SQLAB #SQlatino #SQlatinoamerica #sqldatabase #sqldeveloper #SQLgroupie #sqlimer #sqlimerbymay #sqlinjection #sqlinternals #sqlite #sqlite3 #SQLLearning #SQLMagazine #sqlmanagementstudio #sqlmanager #Sqlmap #sqlrun #sqlsaturday2017 #sqlsatvienna #sqlserver #SQLserver2012 #sqlserver2014 #sqlserver2017 #sqlserver2022 #SQLServeronLinux #SQLsolutionsgroup #SQLTraining #SQLYog Events Extended Events learning SQL SQL Administration SQL Internals SQLPASS SQLSaturday SQL Saturday SQL Server SSG SSIS T-SQL
Picture of Jared Kirkpatrick

Jared Kirkpatrick

My life in the tech world started at Dell a little before the Windows 95 release. I started my MCSE there and took the Database track toward a MCDBA and never looked back. I’ve been heads down in data ever since. My first exposure to SQL Server was with SQL 6.5. In addition to schooling at Wentworth Military Academy and two years at Southwestern University in San Marcos, Texas, my certifications are MCP, MCP+I, MCSE, and MCDBA. During my SQL journey, I have been a Supporting Author of SQL Server 2000 Blackbook, presenter at several PASS Chapters and at SQLSaturday, since it was still in the single digits! I was the IBM Principal Consultant for Honeywell and HR Logic, and Senior Developer for Match.com. where I met Randy Knight who started SQL Solutions Group! I have extensive background with medical systems for employers like Maxium, Broadlane, Medassets, Press Gainey, and Florida Cancer Specialists to name a few. When I am not working I spend as much time as I can with my family outdoors.

Leave a Reply

Related Articles

A blue and white background with white text Description automatically generated

Exploring SSMS 21

Microsoft recently announced SQL Server Management Studio (SSMS 21). Here’s a quick look at what’s included in this latest iteration.

Read More »
December 13, 2024