TSQL logging inside transaction using CLR stored procedure

Question

I'm trying to write a log into another database inside a transaction so that the log will survive even if the transaction is rolled back. I've read this answer which says:

One possibility is to use a CLR stored procedure to do the logging. This can open its own connection to the database outside the transaction and enter and commit the log data.

So I created CLR stored procedure using this article:

[SqlProcedure]
public static void Voice(SqlString procedureName, SqlInt32 id)
{
    Connection = new SqlConnectionStringBuilder();
    Connection.ContextConnection = true;

    using (TransactionScope transScope = new TransactionScope())
    {                
        using (SqlConnection conn = new SqlConnection(Connection.ToString()))
        {
            conn.Open();                    
            SqlCommand cmdInsert = conn.CreateCommand();
            cmdInsert.CommandText = sql;
            cmdInsert.Parameters.Add("@id", SqlDbType.Int);
            cmdInsert.Parameters[0].Value = id;         
            cmdInsert.Parameters.Add("@procedureName", SqlDbType.NVarChar);
            cmdInsert.Parameters[1].Value = procedureName;
            cmdInsert.ExecuteNonQuery();                
        }
        transScope.Complete();
    }
}

However, data is not saved afer I executed and rolled back stored procedure in SQL Server:

BEGIN TRAN
    EXEC dbo.SayHelloVoice @id = 1,
                           @procedureName = N'FooProcedure'
ROLLBACK TRAN

We have three environments:

• dev. Server name is Q-SQL001
• test. Server name is Q-SQL002
• prod. Server name is Q-SQL003

So this CLR stored procedure should work on all environments.

Could you say what I am doing wrong? Any help would be greatly appreciated!

UPDATE:

So the work version looks like this. Big thanks to the @Milney:

var serverName = string.Empty;
var dbName = string.Empty;
serverName = SqlExecuteScalar("SELECT @@SERVERNAME");
dbName = SqlExecuteScalar("SELECT DB_NAME()");

SqlConnectionStringBuilder sqlConn = new SqlConnectionStringBuilder();
sqlConn.InitialCatalog = dbName;
sqlConn.DataSource = serverName;
sqlConn.IntegratedSecurity = true;
sqlConn.ContextConnection = false;
sqlConn.Enlist = false;
sqlConn.ApplicationName = "New application";

var sql = "USE FooDatabase
           INSERT INTO dbo.MyTable ..."
using (SqlConnection conn2 = new SqlConnection(sqlConn.ConnectionString))
{
    conn2.Open();                    
    SqlCommand cmdInsert = conn2.CreateCommand();
    cmdInsert.CommandText = sql;
    cmdInsert.Parameters.Add("@id", SqlDbType.Int);
    cmdInsert.Parameters[0].Value = storeTime;
    cmdInsert.Parameters.Add("@messageText", SqlDbType.NVarChar);
    cmdInsert.Parameters[1].Value = messageText;            
    cmdInsert.ExecuteNonQuery();                    
} 

Answer 1

If you use:

Connection.ContextConnection = true;

Then it's going to use the same connection that the CLR Sproc is running in - you need to open a new connection.