TransactionScope MSDTC has been disabled error only on 2nd ExecRea

  • Thread starter Thread starter exe
  • Start date Start date
E

exe

Hi,

I'm using TransactionScope to do a serie Insert, Update, Delete operations
with the ExecuteNonQuery and some Selects with ExecuteScalar everything works
fine with multiple connections in the same scope.
Problem is that when i do two Selects with ExecuteReader (to fill datasets)
it shows the folowing error:
Exception:
Click to expand...
Network access for Distributed Transaction Manager (MSDTC) has been
disabled. Please enable DTC for network access in the security configuration
for MSDTC using the Component Services Administrative tool.
InnerException:
Click to expand...
The transaction manager has disabled its support for remote/network
transactions.

Im'm working with SQL2005 and .net3.5
 
So something is causing the LTM to escalate to DTC; this isn't unusual
(although SQL2005 supports promotable transactions for many scenarios), and
will require DTC to be enabled with visibility to/from the db servers
(bidirectional).

Are the two commands using the same (identical) connection string? Of
course, if you can close the connection it might be able to re-use the
physical connection from the pool. If you are *already* trying to use the
same connection, is MARS enabled?

Marc
 
Are the two commands using the same (identical) connection string? Yes.
If you are *already* trying to use the same connection, is MARS enabled? I
am, but do know nothing about MARS... Could you explain?

Thanks Marc
 
MARS is Multiple Active Result Sets, and allows for 2 or more active queries
on the same connection (within limits). Of course, if you can serialize your
requests life is easier...
You enable MARS by including "MultipleActiveResultSets=True" in your
connection string.
Anyways, it looks like this might have raised a different error, so maybe
enabling DTC is your best bet...
http://msdn2.microsoft.com/en-us/library/ms345109.aspx

Marc
 
You are right after enabling inabound and outbound Transaction Manager
Comunication, it works.

Still i can't understand why only the readers go to DTS. I mean i use the
same transactionScope for several ExecuteNonQuerys and it doesn't need DTS,
it should right?
 
Are you perhaps leaving your readers open? It is unfortunately quite hard to
tell without some code... I'll try and run a few tests later to see if I can
figure anything out...

Marc
 
No i do not leave the readers open.

The code is like this:
//the connectionstring is always the same
private IDataReader dataBaseReader;

function:
using (TransactionScope ts = new TransactionScope())
{
//READ
if (dataBaseReader != null && !dataBaseReader.IsClosed)
dataBaseReader.Close();
if (dataBaseConnection.State != ConnectionState.Open)
{
dataBaseConnection.ConnectionString = connectionString;
dataBaseConnection.Open();
}
command.Connection = dataBaseConnection; //Goes to remote DBServer
dataBaseReader = command.ExecuteReader(
CommandBehavior.CloseConnection);
dt.Table[0].Load(dataBaseReader, LoadOption.Upsert);
dataBaseReader.Close();

//INSERT
if (dataBaseReader != null && !dataBaseReader.IsClosed)
dataBaseReader.Close();
if (dataBaseConnection.State != ConnectionState.Open)
{
dataBaseConnection.ConnectionString = connectionString;
dataBaseConnection.Open();
}
command.Connection = dataBaseConnection;
int rowsAffected = command.ExecuteNonQuery();

if (dataBaseReader != null && !dataBaseReader.IsClosed)
dataBaseReader.Close();
if (dataBaseConnection.State != ConnectionState.Open)
{
dataBaseConnection.ConnectionString = connectionString;
dataBaseConnection.Open();
}
command.Connection = dataBaseConnection;
object result = command.ExecuteScalar();


//READ
if (dataBaseReader != null && !dataBaseReader.IsClosed)
dataBaseReader.Close();
if (dataBaseConnection.State != ConnectionState.Open)
{
dataBaseConnection.ConnectionString = connectionString;

///EXCEPTION!!!
dataBaseConnection.Open();
///EXCEPTION!!!

}
command.Connection = dataBaseConnection; //Goes to remote DBServer
dataBaseReader = command.ExecuteReader(
CommandBehavior.CloseConnection);
dt.Table[0].Load(dataBaseReader, LoadOption.Upsert);
dataBaseReader.Close();
}


Thank for everything you have been very helpful.
 
Back
Top