471,088 Members | 1,295 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,088 software developers and data experts.

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
Mar 12 '08 #1
6 2827
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
Mar 12 '08 #2
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
Mar 12 '08 #3
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
Mar 12 '08 #4
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
Mar 12 '08 #5
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
Mar 12 '08 #6
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
Mar 12 '08 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by cylt | last post: by
4 posts views Thread by hardieca | last post: by
3 posts views Thread by Aleksey Timonin | last post: by
2 posts views Thread by GaryDean | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.