TransactionScop e MSDTC has been disabled error only on 2nd ExecRea

[=?Utf-8?B?ZXhl?=]

Hi,

I'm using TransactionScop e 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

[=?Utf-8?B?ZXhl?=]

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" wrote:

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

[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 "MultipleActive ResultSets=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

[=?Utf-8?B?ZXhl?=]

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 transactionScop e for several ExecuteNonQuery s and it doesn't need DTS,
it should right?

"Marc Gravell" wrote:

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 "MultipleActive ResultSets=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

[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

[=?Utf-8?B?ZXhl?=]

No i do not leave the readers open.

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

function:
using (TransactionSco pe ts = new TransactionScop e())
{
//READ
if (dataBaseReader != null && !dataBaseReader .IsClosed)
dataBaseReader. Close();
if (dataBaseConnec tion.State != ConnectionState .Open)
{
dataBaseConnect ion.ConnectionS tring = connectionStrin g;
dataBaseConnect ion.Open();
}
command.Connect ion = dataBaseConnect ion; //Goes to remote DBServer
dataBaseReader = command.Execute Reader(
CommandBehavior .CloseConnectio n);
dt.Table[0].Load(dataBaseR eader, LoadOption.Upse rt);
dataBaseReader. Close();

//INSERT
if (dataBaseReader != null && !dataBaseReader .IsClosed)
dataBaseReader. Close();
if (dataBaseConnec tion.State != ConnectionState .Open)
{
dataBaseConnect ion.ConnectionS tring = connectionStrin g;
dataBaseConnect ion.Open();
}
command.Connect ion = dataBaseConnect ion;
int rowsAffected = command.Execute NonQuery();

if (dataBaseReader != null && !dataBaseReader .IsClosed)
dataBaseReader. Close();
if (dataBaseConnec tion.State != ConnectionState .Open)
{
dataBaseConnect ion.ConnectionS tring = connectionStrin g;
dataBaseConnect ion.Open();
}
command.Connect ion = dataBaseConnect ion;
object result = command.Execute Scalar();
//READ
if (dataBaseReader != null && !dataBaseReader .IsClosed)
dataBaseReader. Close();
if (dataBaseConnec tion.State != ConnectionState .Open)
{
dataBaseConnect ion.ConnectionS tring = connectionStrin g;

///EXCEPTION!!!
dataBaseConnect ion.Open();
///EXCEPTION!!!

}
command.Connect ion = dataBaseConnect ion; //Goes to remote DBServer
dataBaseReader = command.Execute Reader(
CommandBehavior .CloseConnectio n);
dt.Table[0].Load(dataBaseR eader, LoadOption.Upse rt);
dataBaseReader. Close();
}
Thank for everything you have been very helpful.
"Marc Gravell" wrote:

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