Capture and log error of TSQL code in SQL Server 2012
SQL Server provides a robust way to capture error with TRY….CATCH but I wanted to have centralized information about error to maintain history and for debugging purpose so that I have created one stored procedure to log error information in one table. I used to call that stored procedure which capture error in my every stored procedure.
I have created “[dbo].[usp_LogErrorHistory] ” stored procedure which logs all errors in “[dbo].[ErrorHistory]” table. I used to call “[dbo].[usp_LogErrorHistory] ” SP in every stored procedure I create and hence I have, now, one central location (“[dbo].[ErrorHistory]” table) where I can go and look for total number of error withing given time period and for given objects.
Let us create “[dbo].[ErrorHistory]” table and “[dbo].[usp_LogErrorHistory] ” stored procedure with following TSQL:
CREATE TABLE [dbo].[ErrorHistory]( [ErrorLogID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL, [ErrorTime] [datetime] NOT NULL DEFAULT GETDATE(), [UserName] [sysname] NOT NULL, [ErrorNumber] [int] NOT NULL, [ErrorSeverity] [int] NULL, [ErrorState] [int] NULL, [ErrorProcedure] [nvarchar](126) NULL, [ErrorLine] [int] NULL, [ErrorMessage] [nvarchar](4000) NOT NULL, [ContextInfo] [varbinary](max) NULL, CONSTRAINT [PK_ErrorLog_ErrorLogID] PRIMARY KEY CLUSTERED ( [ErrorLogID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ) GO CREATE PROCEDURE [dbo].[usp_LogErrorHistory] -- contains the ErrorLogID of the row inserted @ErrorLogID [int] = 0 OUTPUT AS BEGIN SET NOCOUNT ON; -- Output parameter value of 0 indicates that error -- information was not logged SET @ErrorLogID = 0; BEGIN TRY -- Return if there is no error information to log IF ERROR_NUMBER() IS NULL 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 PRINT 'Cannot log error since the current transaction is in an uncommittable state. ' + 'Rollback the transaction before executing usp_LogErrorHistory in order to successfully log error information.'; RETURN; END INSERT [dbo].[ErrorHistory] ( [UserName], [ErrorNumber], [ErrorSeverity], [ErrorState], [ErrorProcedure], [ErrorLine], [ErrorMessage], [ContextInfo] ) VALUES ( CONVERT(sysname, CURRENT_USER), ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_PROCEDURE(), ERROR_LINE(), ERROR_MESSAGE(), CONTEXT_INFO() ); -- Pass back the ErrorLogID of the row inserted SET @ErrorLogID = @@IDENTITY; END TRY BEGIN CATCH PRINT 'An error occurred in stored procedure usp_LogErrorHistory: '; RETURN -1; END CATCH END; GO
Once we are done with above given SP and table, we will create one more user stored procedure which we will execute and it will generate an error which will be logged in our error history table.
CREATE PROC TestErrorLog1 AS BEGIN --Specifies whether SQL Server automatically rolls back the current transaction --when a Transact-SQL statement raises a run-time error. --this is the reason I keep having XACT_ABORT in all my SP when transaction is used SET XACT_ABORT ON; --used to keep following statement in all my SP for performance point of view SET NOCOUNT ON BEGIN TRY --I am not performing any DML statement here though I have kept transaction here --to demonstrate the usage BEGIN TRANSACTION SELECT 1/0 COMMIT TRANSACTION END TRY BEGIN CATCH IF @@TRANCOUNT>0 ROLLBACK --to log the error EXEC [dbo].[usp_LogErrorHistory] ; --to show the error to the end user THROW END CATCH END GO
Now this is a time to check functionality of our error SP by executing following TSQL script:
--execut TestErrorLog1 SP to confirm whether error is being logged or not EXECUTE TestErrorLog1 GO
Okay so I hope your first error after creating this environment has logged. Let us confirm it by executing SELECT query in Error History table.
--Looking at [dbo].[ErrorHistory] table to see captured error: SELECT * FROM [dbo].[ErrorHistory]
Here is the output of my SELECT statement on ErrorHistory table.
Please note that This code should work in older version of SQL Server eg: SQL Server 2008 or 2008 R2 as well. You have to use RAISERROR instead of THROW statement.
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles.