My Application executes multiple sql commands and stored procedures in multiple, sometimes linked Databases. Every call has his own Thread inside the program.
Some calls i want to execute in a transaction of type snapshot. The Database where this calls are executed have snapshot enabled.
This is how i have implemented it:
...
var set = new DataSet();
var dt = new DataTable();
var adapter = new SqlDataAdapter();
errMsg = "";
exceptionString = "";
var transactionOptions = new TransactionOptions();
transactionOptions.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted; //default
string query = executionString;
if (condition)
{
transactionOptions.IsolationLevel = System.Transactions.IsolationLevel.Snapshot;
}
using (var scope = new TransactionScope(TransactionScopeOption.Required, transactionOptions))
{
try
{
using (SqlConnection cnn = new SqlConnection(connectionString))
{
cnn.Open();
using (SqlCommand cmd = new SqlCommand(query, cnn))
{
cmd.CommandTimeout = timeoutInSeconds;
adapter.SelectCommand = cmd;
adapter.Fill(set);
dt = set.Tables[0];
}
if (othercondition && followupQuery != null)
{
query = followupQuery;
using (SqlCommand cmd = new SqlCommand(query, cnn))
{
cmd.ExecuteNonQuery();
}
}
scope.Complete();
}
}
catch (Exception ex)
{
scope.Dispose();
errMsg = "Exception while running SQL query " + query + ": " + Environment.NewLine + ex.Message;
exceptionString = errMsg + Environment.NewLine + ex.ToString();
logger.log().Error("Exception while running SQL query " + query + ": ", ex);
}
}
return dt;
My problem is now, that sometimes other procedures which don't even use this method get a 'SqlException: Remote access is not supported for transaction isolation level "SNAPSHOT".' exception. The Servers where they are stored are linked and don't have snapshot enabled. But why is snapshot active for theese calls in the first place? Are there sometype of connectionpools in the background active? How can i make shure the transaction-level is only used for the calls which need them and how can i set it back correctly? Thanks in advance
