BUG: BeginTransaction() changes the isolation level for subsequent transactions (309544)

RESOLUTION

• Close and re-open the connection. If you are using connection pooling (which is the default), make sure that you did not specify "connection reset=false" in the connect string. "Connection Reset" is "true" by default.
• Run a SQL statement through a SqlCommand object to manually reset the transaction isolation level as follows:

  [Microsoft Visual Basic .NET]
  
  Dim resetCMD As New SqlCommand("SET TRANSACTION ISOLATION LEVEL READ COMMITTED", sqlConn)
  resetCMD.ExecuteNonQuery()
  					

  [Microsoft Visual C# .NET]
  
  SqlCommand resetCMD = new SqlCommand("SET TRANSACTION ISOLATION LEVEL READ COMMITTED", sqlConn);
  resetCMD.ExecuteNonQuery();
  					

  [Microsoft Visual C++ .NET]
  
  SqlCommand resetCMD = new SqlCommand("SET TRANSACTION ISOLATION LEVEL READ COMMITTED", sqlConn);
  resetCMD.ExecuteNonQuery();
  					

  [Microsoft JScript .NET]
  
  var resetCMD:SqlCommand = new SqlCommand("SET TRANSACTION ISOLATION LEVEL READ COMMITTED", sqlConn);
  resetCMD.ExecuteNonQuery();
  					

• Call BeginTransaction, and immediately follow this with Commit. The default is to open with IsolationLevel.ReadCommitted; however, you do not have to specify this value explicitly. For example:

  [Visual Basic .NET]
  
  Dim tempTrans As SqlTransaction = sqlConn.BeginTransaction()
  tempTrans.Commit()
  					

  [Visual C# .NET]
  
  SqlTransaction tempTrans = sqlConn.BeginTransaction();
  tempTrans.Commit();
  					

  [Visual C++ .NET]
  
  SqlTransaction tempTrans = sqlConn.BeginTransaction();
  tempTrans.Commit();
  					

  [JScript .NET]
  
  var tempTrans:SqlTransaction = sqlConn.BeginTransaction();
  tempTrans.Commit();