Here is a script that demonstrates an ADO-based approach for logging within a transaction that may later be rolled back.
This script creates dbo.spLogErrorCommitted and other needed objects, and prints out some instructions.
/*
Script to create an error logging procedure that can record errors
that will retain the error messages even when they were written within
a transaction that is subsequently rolled back.
Written by David Rueter (drueter@assyst.com) 3/28/2013
Not copyrighted / Public domain / Use at your own risk.
*/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
To remove objects created by this script:
DROP PROCEDURE dbo.spLogErrorCommitted
DROP PROCEDURE dbo.spLogError
DROP TABLE dbo.tblErrorLog
DROP USER errorWriter
DROP LOGIN errorWriter
*/
/*
Create a table to hold error log messages.
*/
CREATE TABLE tblErrorLog (
ErrorLogID int IDENTITY PRIMARY KEY,
DateLogged datetime DEFAULT (GETDATE()),
Msg varchar(MAX)
)
GO
/*
Create a procedure to insert errror messages into the log table
*/
CREATE PROCEDURE dbo.spLogError
@Msg varchar(MAX)
AS
BEGIN
SET NOCOUNT ON
INSERT INTO tblErrorLog (Msg) VALUES (@Msg)
END
GO
/*
Create a new SQL login for ADO to use to call back into the database. This user will ONLY have rights to execute the dbo.spLogError procedure.'
*/
DECLARE @SQL varchar(MAX)
SET @SQL = 'CREATE LOGIN errorWriter WITH PASSWORD=N''errorWriterPW'', DEFAULT_DATABASE=[' + DB_NAME() + '], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF'
EXEC(@SQL)
/*
Create a new SQL user in the current database linked to the new login created above.
*/
CREATE USER [errorWriter] FOR LOGIN [errorWriter] WITH DEFAULT_SCHEMA=[dbo]
GO
/*
Grant rights to the new errorWriter user to execute the dbo.spLogError procedure
*/
GRANT EXEC ON dbo.spLogError TO errorWriter
GO
/*
Create the procedure that will be called to log and commit an error message even inside
a transaction that will be rolled back.
*/
CREATE PROCEDURE dbo.spLogErrorCommitted
@Msg varchar(MAX),
@SuppressInternalErrors bit = 0
AS
BEGIN
/*
This procedure is designed to allow a caller to provide a message that will be written to an error log table,
and allow the caller to call it within a transaction. The provided message will be persisted to the
error log table even if the transaction is rolled back.
To accomplish this, this procedure utilizes ADO to establish a second database connection (outside
the transaction context) back into the database to call the dbo.spLogError procedure.
*/
SET NOCOUNT ON
DECLARE @ConnStr varchar(MAX)
--connection string for ADO to use to access the database
SET @ConnStr = 'Provider=SQLNCLI10; DataTypeCompatibility=80; Server=localhost; Database=' + DB_NAME() + '; Uid=errorWriter; Pwd=errorWriterPW;'
DECLARE @SQLCommand varchar(MAX)
SET @SQLCommand = 'EXEC dbo.spLogError @Msg=''' + REPLACE(@Msg, CHAR(39), CHAR(39) + CHAR(39)) + ''''
DECLARE @ObjCn int
--ADO Connection object
DECLARE @ObjRS int
--ADO Recordset object returned
DECLARE @RecordCount int
--Maximum records to be returned
SET @RecordCount = 0
DECLARE @ExecOptions int
--Execute options: 0x80 means to return no records (adExecuteNoRecords) + 0x01 means CommandText is to be evaluted as text
SET @ExecOptions = 0x81
DECLARE @LastResultCode int = NULL
--Last result code returned by an sp_OAxxx procedure. Will be 0 unless an error code was encountered.
DECLARE @ErrSource varchar(512)
--Returned if a COM error is encounterd
DECLARE @ErrMsg varchar(512)
--Returned if a COM error is encountered
DECLARE @ErrorMessage varchar(MAX) = NULL
--our formatted error message
SET @ErrorMessage = NULL
SET @LastResultCode = 0
BEGIN TRY
EXEC @LastResultCode = sp_OACreate 'ADODB.Connection', @ObjCn OUT
IF @LastResultCode <> 0 BEGIN
EXEC sp_OAGetErrorInfo @ObjCn, @ErrSource OUTPUT, @ErrMsg OUTPUT
END
END TRY
BEGIN CATCH
SET @ErrorMessage = ERROR_MESSAGE()
END CATCH
BEGIN TRY
IF @LastResultCode = 0 BEGIN
EXEC @LastResultCode = sp_OAMethod @ObjCn, 'Open', NULL, @ConnStr
IF @LastResultCode <> 0 BEGIN
EXEC sp_OAGetErrorInfo @ObjCn, @ErrSource OUTPUT, @ErrMsg OUTPUT
END
END
END TRY
BEGIN CATCH
SET @ErrorMessage = ERROR_MESSAGE()
END CATCH
IF @LastResultCode = 0 BEGIN
EXEC @LastResultCode = sp_OAMethod @ObjCn, 'Execute', @ObjRS OUTPUT, @SQLCommand, @RecordCount, @ExecOptions
IF @LastResultCode <> 0 BEGIN
EXEC sp_OAGetErrorInfo @ObjCn, @ErrSource OUTPUT, @ErrMsg OUTPUT
END
END
IF @ObjRS IS NOT NULL BEGIN
BEGIN TRY
EXEC sp_OADestroy @ObjCn
END TRY
BEGIN CATCH
--not much we can do...
SET @LastResultCode = 0
END CATCH
END
IF @ObjCn= 1 BEGIN
BEGIN TRY
EXEC sp_OADestroy @ObjCn
END TRY
BEGIN CATCH
--not much we can do...
SET @LastResultCode = 0
END CATCH
END
IF (@SuppressInternalErrors = 1) AND ((@LastResultCode <> 0) OR (@ErrorMessage IS NOT NULL)) BEGIN
SET @ErrorMessage = 'Error in spLogErrorCommitted' + ISNULL(': ' + @ErrMsg, '')
RAISERROR(@ErrorMessage, 16, 1)
--We are in an error-logging routine. If the we encounter an error here, there's not a lot we can do.
--Don't raise an error or try to log this internal error.
END
END
GO
DECLARE @CRLF varchar(5)
SET @CRLF = CHAR(13) + CHAR(10)
DECLARE @Instructions varchar(MAX)
SET @Instructions =
'You can now call dbo.spLogErrorCommitted to write error messages to dbo.tblErrorLog.' + @CRLF +
'Messages written via this procedure will be retained even if written within a transaction' + @CRLF +
'that is rolled back.' + @CRLF +
@CRLF +
'To demonstrate that this works, try executing the following:' + @CRLF +
'
BEGIN TRAN
BEGIN TRY
RAISERROR(''Sample Error for Testing'', 16, 1)
COMMIT TRAN
END TRY
BEGIN CATCH
DECLARE @ThisMsg varchar(MAX)
SET @ThisMsg = ''We had an error: '' + ERROR_MESSAGE()
EXEC dbo.spLogErrorCommitted @Msg = @ThisMsg
ROLLBACK TRAN
END CATCH
SELECT * FROM dbo.tblErrorLog' + @CRLF +
@CRLF +
'Note that the row still exists in dbo.tblErrorLog, even though ROLLBACK TRAN was called' + @CRLF +
'after the message was inserted into a row in the table.' + @CRLF +
@CRLF +
'Remember that this stored procedure uses OLE. To work properly you may need to configure ' +
'your database to allow OLE, as follows: ' + @CRLF +
' EXEC sp_configure ''show advanced options'', 1;' + @CRLF +
' RECONFIGURE;' + @CRLF +
' EXEC sp_configure ''Ole Automation Procedures'', 1;' + @CRLF +
' RECONFIGURE;' + @CRLF +
'Also, your SQL user must have execute rights to the following stored procedures in master:' + @CRLF +
' sp_OACreate' + @CRLF +
' sp_OAGetProperty' + @CRLF +
' sp_OASetProperty' + @CRLF +
' sp_OAMethod' + @CRLF +
' sp_OAGetErrorInfo' + @CRLF +
' sp_OADestroy' + @CRLF +
'You can grant rights for each of these as follows:' + @CRLF +
' USE master' + @CRLF +
' GRANT EXEC ON sp_OACreate TO myuser' + @CRLF +
' GRANT EXEC etc. ...' + @CRLF
PRINT @Instructions