473,218 Members | 1,482 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,218 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 2979
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Rick_Kierner | last post by:
I am attempting to use the TransactionScope class to manage my transactions. I am running into an issue. My web server is in an NT work group. My SQL Server is in a domain. When my code...
6
by: cylt | last post by:
Hello, I'm using a TransactionScope with a ADO.NET SqlClient and i get the error : System.Runtime.InteropServices.COMException (0x8004D024): The transaction manager has disabled its support for...
2
by: Spottswoode | last post by:
Hi I have developed a client/server application in C# that worked perfectly in my office but not so perfectly after deploying it to our client. A little information about the architecture first:...
4
by: hardieca | last post by:
Hi, I'd really like to use TransactionScope within the Business Layer of my web application. A book I'm reading makes a note that it should not be used in a shared web hosting environment...
4
by: Bexm | last post by:
Hello I am having problems with the transactionscope that is around alot of the code I have it working fine on our local machines and our Dev server, but when we put it on our live server we get...
3
by: Aleksey Timonin | last post by:
Hi guys, I tried to use TransactionScope on to defferent TableAdapters like this: using (TransactionScope transScope = new TransactionScope(TransactionScopeOption.Required)) {...
3
by: Michael Schöller | last post by:
Hello, First of all english is not my natural language so please fogive me some bad mistakes in gramatic and use of some vocables :). I have a great problem here. Well I will not use it...
2
by: GaryDean | last post by:
When I use transactions with sql server I usually do this... using (TransactionScope scope = new TransactionScope) { using (SqlConnection1 = new SqlConnection . . . . . and this all works...
2
by: G.S. | last post by:
Is there a way to use transactions from within C# code without using MSDTC (client-and-server environment where the SQL server is behind a firewall)? Thank you
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

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.