CODEWITHSUNDEEP
c#sql-server
Dryadwoods
Dryadwoods

Reputation: 2929

con.Open() fails exception Transactions with IsolationLevel Snapshot cannot be promoted

The following code is working when the application "use" is "low", the insert is happening.

As soon as I am having high use, I'm getting the error

Transactions with IsolationLevel Snapshot cannot be promoted

when I try to open the connection. The exception is happening on the line "cn.Open()";

I have tried to set the database with snapshot_isolation_state with 0 or with 1, or is_read_commited_snapshot_on with 0 or 1.... and I even have tried to use a transaction on the C# code... but it made no difference.

I do not care about transaction in this case, I just want to make inserts (no updates)....

How can I fix this?

My code: 

var connectionString = @"data source=XXXX\SQL2005;initial catalog=pw_LogDBStack;user id=XXXX;password=XXXX;";
string query = "INSERT INTO LogEntry (DateTime, CallerMethod, Stack, Query) VALUES ( @DateTime, @CallerMethod, @Stack, @Query)";
//SqlTransaction transaction = null;
using (SqlConnection cn = new SqlConnection(connectionString))
{
    cn.Open();
    //transaction = cn.BeginTransaction(System.Data.IsolationLevel.ReadUncommitted);
    //using (SqlCommand cmd = new SqlCommand(query, cn, transaction))
    using (SqlCommand cmd = new SqlCommand(query, cn))
    {
        cmd.Parameters.AddWithValue("@DateTime", DateTime.Now);
        cmd.Parameters.AddWithValue("@CallerMethod", callerMethod);
        cmd.Parameters.AddWithValue("@Stack", stackStr);
        cmd.Parameters.AddWithValue("@Query", c.Command.ToTraceString());
        cmd.ExecuteNonQuery();
    }
    //transaction.Commit();
    cn.Close();
}

Upvotes: 2

Views: 861

Answers (1)

Dryadwoods
Dryadwoods

Reputation: 2929

I was able to fix this by adding the property to the connection string Enlist=false;

https://www.connectionstrings.com/all-sql-server-connection-string-keywords/

Upvotes: 3

Related Questions