The fundamental problem with writing an error to a log within a transaction that is subsequently rolled back is that everything written by the connection within the transaction is rolled back--including the error message.

So the only solution is to either wait until after the end of the transaction (i.e. perform the ROLLBACK and then log the error), or to use a different connection that is not part of the transaction.

One way to use a different transaction is to use CLR (as steven.bingham posted).

The message will remain in dbo.tblErrorLog even though the transaction was rolled back.

I'm happy to provide the declaration of dbo.spLogErrorCommitted if anyone is interested. But the basic idea is that it uses ADO (via sp_OACreate) to establish a new database connection and to perform the insert into the log table.

In this way the inserted error log row is not removed when the transaction is rolled back.

Has anyone measured the performance cost of either of these techniques (Steve's CLR using a seperate transaction context and David's approach to use ADO) ? This probably won't be that critical if they are restricted to pure exception handling, but it's another situation if this would be used for more general logging purposes.

I haven't measured performance for this specific routine, but I have compared performance between CLR and COM calls in the past (i.e. for retrieving HTML from a remote server).

CLR is faster--and is the preferred approach suggested by Microsoft and most developers.

COM is simpler to deploy, and performs adequately for many things. With COM you can use pure T-SQL code, and don't need to register any assemblies on the server.

To illustrate the performance difference: a certain procedure using COM can execute 200 times in 22 seconds. The same procedure written using CLR can execute 200 times in 13 seconds.

So CLR is definitely faster and "better"...once it is written and deployed. Using COM is simpler to write and deploy if you prefer working in T-SQL and are not in the business of writing C# code and deploying .NET assemblies and CLR stored procedures.

I too would like to see the declaration of dbo.spLogErrorCommitted. The nice thing aboutthe ADO approach is it's relative simplicity. Although, there are the execute permission considerations in leveraging the sp_OACreate, which if memory serves me correctly is an extended stored proc.

There are also some configuration considerations with the CLR stored proc. approach. Some functionality in this CLR stored proc. requires it to have an assembly permission option of 'EXTERNAL_ACCESS'. With this permission option you will also either have to sign your assembly with a key OR set the database 'Trustworthy' setting to true. Either way you'll want your DBA's blessing.

The best approach is the one that works best for you!

As for the performance issue, CLR objects often aren't that bad for performance. In this case given the nature of exceptions you likely won't be running this often or frequent enough for performance to be an issue.

/*Create a procedure to insert errror messages into the log table*/CREATE PROCEDURE dbo.spLogError@Msg varchar(MAX)ASBEGIN 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 insidea transaction that will be rolled back.*/

CREATE PROCEDURE dbo.spLogErrorCommitted@Msg varchar(MAX),@SuppressInternalErrors bit = 0AS 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. */

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

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