Our Data Warehouse makes use of both SSIS packages and T-SQL Stored Procedures during the ETL phase. I use an audit schema borrowed from the excellent book SSIS Problem -Design-Solution to collect errors generated by SSIS. For T-SQL SP's I needed a simple way to collect errors which could be integrated into our DBA Monitoring Dashboard. This is what I did.
In the event of an error the CATCH block starting on line 56 is executed.
Within the CATCH block I assign the ERROR_ values to local variables on line 58-63.
On lines 64-65 I then EXECUTE the InsertErrorLog procedure with the local variables passed as a parameter. A row is then added to the table Metadata.ErrorLog
In my team we have a daily monitoring report which allows us to see problems in the overnight processing before and users report issues. This gives us a chance to get ahead of the problem by either fixing it or communicating with stakeholders. Before this was implemented we faced the spectacle of users periodically asking, "Is there a problem with the Data Warehouse.". Nowadays we can answer with certainty.
Feel free to use the code and please email me any improvements you make.
- Identified what data I wanted to capture.
- Built a table to store the errors
- Built a Stored Procedure to INSERT the errors
- Embedded the SP in the CATCH block of a TRY...CATCH in every Data Warehouse Procedure
What Error Data
Keep it simple, I thought. I needed to know what happended when. Who executed which procedure on what server. What was the error and where within the procedure did the error occur.
Most of that data can be collected using the built-in T-SQL ERROR_ system functions. This includes the error number, the line number on which the error occured, the severity, the state, which procedure caused the error and a full error message. The remaining details could be collected by accessing the system user, server name and setting a default on a created date time column.
Most of that data can be collected using the built-in T-SQL ERROR_ system functions. This includes the error number, the line number on which the error occured, the severity, the state, which procedure caused the error and a full error message. The remaining details could be collected by accessing the system user, server name and setting a default on a created date time column.
The Table Design
I used the data types specified for the ERROR_ system functions on MSDN and created a surrogate primary key to ensure the table has a clustered index. CREATE TABLE [Metadata].[ErrorLog](
[ErrorLogID] [int] IDENTITY(1,1) NOT NULL,
[SystemUser] [nvarchar](128) NOT NULL,
[CreatedDateTime] [datetime] NOT NULL,
[ServerName] [nvarchar](128) NOT NULL,
[ErrorNumber] [int] NOT NULL,
[ProcedureName] [nvarchar](128) NOT NULL,
[LineNumber] [int] NOT NULL,
[Severity] [int] NOT NULL,
[State] [int] NOT NULL,
[Message] [nvarchar](4000) NOT NULL,
CONSTRAINT [PK_ErrorLog] PRIMARY KEY CLUSTERED
(
[ErrorLogID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [Metadata].[ErrorLog] ADD DEFAULT (getdate()) FOR [CreatedDateTime]
GO
The Procedure Design
Again, keeping it as simple as possible and generic enough to be used across any solution was the main aim.
Most of the details will be passed through as parameters from the procedure which has encountered an error with the exception of Server Name and System User which I'll SET in this procedure.
The most interesting part of this procedure is the use of SERVERPROPERTY which can be configured to return a variety of useful information about the server environment. Be careful when using it to CONVERT it in order to assign it to a local variable as the returned data is in the form of a SQL_VARIANT. The rest of the procedure is pretty standard fare. A simple INSERT really.
Most of the details will be passed through as parameters from the procedure which has encountered an error with the exception of Server Name and System User which I'll SET in this procedure.
The most interesting part of this procedure is the use of SERVERPROPERTY which can be configured to return a variety of useful information about the server environment. Be careful when using it to CONVERT it in order to assign it to a local variable as the returned data is in the form of a SQL_VARIANT. The rest of the procedure is pretty standard fare. A simple INSERT really.
/*
**********************************************************************************************************************************************************************************************************************
DW - InsertErrorLog
**********************************************************************************************************************************************************************************************************************
VERSION AUTHOR DATE COMMENTS
**********************************************************************************************************************************************************************************************************************
V1.0 GP 27/03/2012 Initial build of InsertErrorLog which is designed to take error details from inside a CATCH block and pass them to a central ErrorLog table
**********************************************************************************************************************************************************************************************************************
USAGE EXAMPLE
EXEC Common.InsertErrorLog 1234,'ProcedureName',1,17,1,'This is a test error message'
*/
CREATE PROCEDURE [Common].[InsertErrorLog]
(
@ErrorNumber INT,
@ProcedureName NVARCHAR(128),
@LineNumber INT,
@Severity INT,
@State INT,
@Message NVARCHAR(4000)
)
AS
SET NOCOUNT ON
DECLARE @SystemUser AS NVARCHAR(128)
DECLARE @ServerName AS NVARCHAR(128)
SET @SystemUser = SUSER_NAME()
SET @ServerName = CONVERT(SYSNAME, SERVERPROPERTY('SERVERNAME'))
-- POPULATE ERROR TABLE WITH SUBMITTED DATA
INSERT INTO DataWarehouse.Metadata.ErrorLog (SystemUser,ServerName,ErrorNumber,ProcedureName,LineNumber,Severity,State,Message)
VALUES (@SystemUser,@ServerName,@ErrorNumber,@ProcedureName,@LineNumber,@Severity,@State,@Message)
GO
An Example Procedure
Let's step through an example starting on line 22 where we enter the body of this stored procedure which is designed to TRUNCATE and repopulate a Time Dimension. On line 22 the BEGIN TRY is entered an all the subsequent code to line 54 is executed.In the event of an error the CATCH block starting on line 56 is executed.
Within the CATCH block I assign the ERROR_ values to local variables on line 58-63.
On lines 64-65 I then EXECUTE the InsertErrorLog procedure with the local variables passed as a parameter. A row is then added to the table Metadata.ErrorLog
In my team we have a daily monitoring report which allows us to see problems in the overnight processing before and users report issues. This gives us a chance to get ahead of the problem by either fixing it or communicating with stakeholders. Before this was implemented we faced the spectacle of users periodically asking, "Is there a problem with the Data Warehouse.". Nowadays we can answer with certainty.
1: USE [DataWarehouse]
2: GO
3: /****** Object: StoredProcedure [DataMart].[LoadDIMTime] Script Date: 03/28/2012 22:56:55 ******/
4: SET ANSI_NULLS ON
5: GO
6: SET QUOTED_IDENTIFIER ON
7: GO
8: /*
9: ***************************************************************************************************************************************************************
10: DW - LoadDIMTime
11: ***************************************************************************************************************************************************************
12: VERSION AUTHOR DATE COMMENTS
13: ***************************************************************************************************************************************************************
14: V1.0 GP 10/01/2012 Initial build of DIMTime
15: V1.1 GP 27/03/2012 Added ErrorLog functionality in the CATCH block
16: ***************************************************************************************************************************************************************
17: USAGE EXAMPLE
18: EXEC DataMart.LoadDIMTime
19: */
20: ALTER PROCEDURE [DataMart].[LoadDIMTime]
21: AS
22: BEGIN TRY
23: -- START AFRESH WITH AN EMPTY TABLE
24: TRUNCATE TABLE DataWarehouse.DataMart.DIMTime
25: -- INSERT DIMENSION MEMBERS
26: INSERT INTO DataWarehouse.DataMart.DIMTime
27: ( Hour ,
28: Minute ,
29: Meridiem
30: )
31: SELECT Hour ,
32: Minute ,
33: Meridiem
34: FROM DataWarehouse.DataSource.vwTime
35: OPTION ( MAXRECURSION 0 )
36: -- ADD THE UNKNOWN MEMBER. SUSPEND THE IDENTITY DURING INSERT
37: IF NOT EXISTS ( SELECT TimeKey
38: FROM DataWarehouse.DataMart.DIMTime
39: WHERE TimeKey = -1 )
40: BEGIN
41: SET IDENTITY_INSERT DataWarehouse.DataMart.DIMTime ON
42: INSERT INTO DataWarehouse.DataMart.DIMTime
43: ( TimeKey ,
44: Hour ,
45: Minute ,
46: Meridiem
47: )
48: SELECT -1 AS TimeKey ,
49: 0 AS Hour ,
50: 0 AS Minute ,
51: 'UN' AS Meridiem
52: SET IDENTITY_INSERT DataWarehouse.DataMart.DIMTime OFF
53: END
54: END TRY
55: -- RETURN ERRORS WHEN ERRORS OCCUR
56: BEGIN CATCH
57: -- REPORT ERRORS
58: DECLARE @ErrorNumber INT = ERROR_NUMBER()
59: DECLARE @ErrorProcedure NVARCHAR(128) = ERROR_PROCEDURE()
60: DECLARE @ErrorLine INT = ERROR_LINE()
61: DECLARE @ErrorSeverity INT = ERROR_SEVERITY()
62: DECLARE @ErrorState INT = ERROR_STATE()
63: DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE()
64: EXEC Common.InsertErrorLog @ErrorNumber, @ErrorProcedure, @ErrorLine,
65: @ErrorSeverity, @ErrorState, @ErrorMessage
66: END CATCH
67: GO
