TransactionScope MSDTC has been disabled error only on 2nd ExecRea

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
Wed Mar 12 07:20:42 CDT 2008

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
Wed Mar 12 07:45:03 CDT 2008

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
Wed Mar 12 07:58:24 CDT 2008

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
Wed Mar 12 10:00:02 CDT 2008

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

Marc
Wed Mar 12 10:44:35 CDT 2008

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
Wed Mar 12 13:52:01 CDT 2008

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.


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