TransactionScope MSDTC has been disabled error only on 2nd ExecRea

[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:

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:

The transaction manager has disabled its support for remote/network
transactions.

Im'm working with SQL2005 and .net3.5

 

[Marc Gravell]

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

 

[exe]

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

 

[Marc Gravell]

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

 

[exe]

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?

 

[Marc Gravell]

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

 

[exe]

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.