1

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();                    
} 
Learner
  • 417
  • 6
  • 24
  • 1
    Related: [Commit transaction outside the current transaction (like autonomous transaction in Oracle)](https://stackoverflow.com/questions/45731207/commit-transaction-outside-the-current-transaction-like-autonomous-transaction/45793221#45793221) – Lukasz Szozda Dec 27 '19 at 09:54
  • @LukaszSzozda thanks for the link. However, the CLR stored procedure is what we want – Learner Dec 27 '19 at 09:55
  • 1
    You might think CLR solves your issues but it causes all kinds of other headaches for deployment – Nick.Mc Dec 27 '19 at 12:32
  • yeah, I know. But we've managed to deploy this assembly – Learner Dec 27 '19 at 13:15

1 Answers1

3

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.

Milney
  • 6,253
  • 2
  • 19
  • 33
  • 1
    Yes but your connection is defined as a context one, so its not actually opening a new connection, its 'connecting' to the existing connection – Milney Dec 27 '19 at 09:59
  • 1
    You want to do Connection.ContextConnection = false, and create it with the same connection string var connection = new Connection(Connection.ConnectionString) – Milney Dec 27 '19 at 10:00
  • 1
    Update with your new code then. Sounds like you didn't actually specify the correct connection string... – Milney Dec 27 '19 at 10:04
  • 1
    Since it is a new connection, you have to give it the full connection string – Milney Dec 27 '19 at 10:05
  • 1
    You could open a context connection first, then get the server and database name with (SELECT @@SERVERNAME and SELECT DB_NAME()), then use those to open the second connection? – Milney Dec 27 '19 at 10:16
  • 1
    You appear to be starting a new transaction with using (TransactionScope transScope = new TransactionScope()), just take that out, as the second command will use an implicit transaction anyway. Or pass in TransactionScopeOption.Suppress to make sure it doesnt use existing transactions – Milney Dec 27 '19 at 10:53
  • 1
    @Learner - Even without the TransactionScope? It may be using an existing connection from the pool... try setting sqlConn.ApplicationName = "Something", so it has a different connection string and therefore gets a different new connection – Milney Dec 27 '19 at 11:00
  • 1
    What is your command actually doing - is it using a linked server per chance?? – Milney Dec 27 '19 at 11:06
  • 1
    Hmm this shouldn't happen then... double check you have closed the first (context) connection you use, before you open the second one? and make sure you got rid of any TransactionScope you have in your CLR proc – Milney Dec 27 '19 at 11:10
  • 1
    @Learning - Okay one last thing I can think of - try adding sqlConn.Enlist=false – Milney Dec 27 '19 at 11:14
  • You are genious! Thank you very much!!! I really like your answer! Have a good day! :) – Learner Dec 27 '19 at 11:17