Team,

I have an answer for retaining logging errors during a rollback situation.

I have an associated reply to another post that you can read at this link

http://social.msdn.microsoft.com/Forums/en-US/vstsdb/thread/2ab351c2-00cc-4500-841b-35c3271e040b/.

I use a similiar approach of implementing TRY/CATCH statements in my stored

procedures and submitting the errors to a database table via an error logging

stored procedure. However, I use a CLR stored procedure to handle the logging

to deal with the transaction issue. Here are the details...

Often times you can run stored procedures from many processes, like other stored

procedures, where there are different levels of transactions. The problem is that if you

write to your log table and a rollback occurs, you lose that error record. Even if you write

the log record after a rollback statement, you could lose your record if that stored proc.

itself is running with in inner transaction context and the out transaction rolled back.

This is simply handled by creating a CLR Stored Procedure, because with a CLR Stored

Proc. you can create a seperate transaction context, within which to write the record to

the logging table, and that context is not affected any transaction contexts of the T-SQL

stored procs that were running, not matter how deep your levels of transactions.

From within the CLR stored proc. I get the pertainent information about the database

and server and dynamically construct my connection string, which points back to the

database the stored proc. is on. I then create a seperate connection, in a seperate

transaction context, and write a record to the logging table. You should be able to

infer the schema of the table from the code.

In the CATCH section that calls this CLR stored proc., I write an error to the system

event log if an exception is throw by this stored procedure. The stored proc. is generic

enough that I could use it, as is, on any database as long as it has a consistently structure

error logging table.

CODE SAMPLE BELOW

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using System.Security;

using System.Security.Principal;

using System.Transactions;

using Microsoft.SqlServer.Server;

public partial class LogDatabaseError

{

[Microsoft.SqlServer.Server.SqlProcedure]

public static void usp_clrLogDatabaseError(SqlDateTime ErrorEventDateTime

,SqlInt32 ErrorNumber

,SqlInt32 ErrorSeverity

,SqlInt32 ErrorState

,SqlString ErrorProcedure

,SqlInt32 ErrorLine

,SqlString ErrorMessage)

{

try

{

const string DATASOURCE = "Data Source=";

const string INITIALCATALOG = ";Initial Catalog=";

const string INTEGRATEDSECURITY = ";Integrated Security=True;Enlist=false;";

string dynamicConnString;

string SQL_InstanceName;

string SQL_DatabaseName;

//Establish a connection in the current context to dynamically get the current

//Server and Database Name.

using (SqlConnection sysconn = new SqlConnection("context connection=true"))

{

SqlCommand GetSQLSystemProperties = new SqlCommand();

GetSQLSystemProperties.Connection = sysconn;

sysconn.Open();

//Get the current SQL Server instance name

GetSQLSystemProperties.CommandText = "SELECT @@Servername";

SQL_InstanceName = (string)GetSQLSystemProperties.ExecuteScalar();

//Get the current Database Name

GetSQLSystemProperties.CommandText = "SELECT DB_NAME()";

SQL_DatabaseName = (string)GetSQLSystemProperties.ExecuteScalar();

sysconn.Close();

}

//Dynamically construct the connection string to establish a connection outside

//of the current context, so that any error written to the error table won't be

//rolled back.

dynamicConnString = DATASOURCE + SQL_InstanceName + INITIALCATALOG + SQL_DatabaseName + INTEGRATEDSECURITY;

using (SqlConnection conn = new SqlConnection(dynamicConnString))

{

SqlCommand InsertDatabaseErrorRecord = new SqlCommand();

SqlParameter ErrorEventDateTimeParam = new SqlParameter("@errorEventDateTime", SqlDbType.DateTime);

SqlParameter ErrorNumberParam = new SqlParameter("@errorNumber", SqlDbType.Int);

SqlParameter ErrorSeverityParam = new SqlParameter("@errorSeverity", SqlDbType.Int);

SqlParameter ErrorStateParam = new SqlParameter("@errorState", SqlDbType.Int);

SqlParameter ErrorProcedureParam = new SqlParameter("@errorProcedure", SqlDbType.NVarChar, 200);

SqlParameter ErrorLineParam = new SqlParameter("@errorLine", SqlDbType.Int);

SqlParameter ErrorMessageParam = new SqlParameter("@errorMessage", SqlDbType.NVarChar, 4000);

ErrorEventDateTimeParam.Value = ErrorEventDateTime;

ErrorNumberParam.Value = ErrorNumber;

ErrorSeverityParam.Value = ErrorSeverity;

ErrorStateParam.Value = ErrorState;

ErrorProcedureParam.Value = ErrorProcedure;

ErrorLineParam.Value = ErrorLine;

ErrorMessageParam.Value = ErrorMessage;

InsertDatabaseErrorRecord.Parameters.Add(ErrorEventDateTimeParam);

InsertDatabaseErrorRecord.Parameters.Add(ErrorNumberParam);

InsertDatabaseErrorRecord.Parameters.Add(ErrorSeverityParam);

InsertDatabaseErrorRecord.Parameters.Add(ErrorStateParam);

InsertDatabaseErrorRecord.Parameters.Add(ErrorProcedureParam);

InsertDatabaseErrorRecord.Parameters.Add(ErrorLineParam);

InsertDatabaseErrorRecord.Parameters.Add(ErrorMessageParam);

InsertDatabaseErrorRecord.CommandText =

" INSERT INTO CLIENT.ErrorLog " +

" (ErrorEventDateTime " +

" ,ErrorNumber " +

" ,ErrorSeverity " +

" ,ErrorState " +

" ,ErrorProcedure " +

" ,ErrorLine " +

" ,ErrorMessage) " +

" VALUES " +

" (@errorEventDateTime " +

" ,@errorNumber " +

" ,@errorSeverity " +

" ,@errorState " +

" ,@errorProcedure " +

" ,@errorLine " +

" ,@errorMessage) ";

InsertDatabaseErrorRecord.Connection = conn;

conn.Open();

InsertDatabaseErrorRecord.ExecuteNonQuery();

conn.Close();

}

}

catch (Exception LogException)

{

throw new Exception("The usp_LogDatabaseError error logging routine experienced a problem while attempting to write an error the logging table.", LogException);

}

}

};

I hope this is helpful and happy logging!! :smooooth: