There is already an open DataReader associated with this Command which must be closed first

[Jonny Bergdahl]

I stumbled into a rather strange problem:

private void Fetch()
{
using (SqlConnection connection = new
SqlConnection(Database.ConnectionString))
{
connection.Open();
using (SqlCommand command = connection.CreateCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = @"SELECT " +
....
}
}
}
}

When this code is executed, i get the following exception on the line that
executes connection.Open():

DataPortal.Fetch failed (System.Transactions.TransactionAbortedException:
The transaction has aborted. --->
System.Transactions.TransactionPromotionException: Failure while attempting
to promote transaction. ---> System.Data.SqlClient.SqlException: There is
already an open DataReader associated with this Command which must be closed
first.
at
System.Data.SqlClient.SqlInternalConnectionTds.ExecuteTransactionYukon(TransactionRequest
transactionRequest, String transactionName, IsolationLevel iso,
SqlInternalTransaction internalTransaction, Boolean
isDelegateControlRequest)
at
System.Data.SqlClient.SqlInternalConnectionTds.ExecuteTransaction(TransactionRequest
transactionRequest, String name, IsolationLevel iso, SqlInternalTransaction
internalTransaction, Boolean isDelegateControlRequest)
at System.Data.SqlClient.SqlDelegatedTransaction.Promote()
--- End of inner exception stack trace ---
at System.Data.SqlClient.SqlDelegatedTransaction.Promote()
at
System.Transactions.TransactionStatePSPEOperation.PSPEPromote(InternalTransaction
tx)
at
System.Transactions.TransactionStateDelegatedBase.EnterState(InternalTransaction
tx)
--- End of inner exception stack trace ---
at
System.Transactions.TransactionStateAborted.CheckForFinishedTransaction(InternalTransaction
tx)
at System.Transactions.EnlistableStates.Promote(InternalTransaction tx)
at System.Transactions.Transaction.Promote()
at
System.Transactions.TransactionInterop.ConvertToOletxTransaction(Transaction
transaction)
at System.Transactions.TransactionInterop.GetExportCookie(Transaction
transaction, Byte[] whereabouts)
at System.Data.SqlClient.SqlInternalConnection.EnlistNonNull(Transaction
tx)
at System.Data.SqlClient.SqlInternalConnection.Enlist(Transaction tx)
at System.Data.SqlClient.SqlInternalConnectionTds.Activate(Transaction
transaction)
at
System.Data.ProviderBase.DbConnectionInternal.ActivateConnection(Transaction
transaction)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection
owningConnection)
at
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()

Since I explicitly create a new SqlConnection - how on earth can it be
associated with an open DataReader?

All my data access code follows the above pattern, creating the
SqlConnection in a using clause. That should kill any open DataReaders on
all connections I create, so that even if connection pooling would return a
previously used connection, it would be clean, and not associated with any
open DataReaders.

All code is run inside an ambient MSDTC transaction, as this is all part of
a BizTalk adapter.

Please advice.

Regards;
/jb

 

[sloan]

CommandBehavior.CloseConnection

I would investigate that.



//That should kill any open DataReaders on

all connections I create//

You don't show your complete code. Where are you using your IDataReader?

http://www.knowdotnet.com/articles/schemas2.html



I would get the EnterpriseLibrary.Data framework. They've done all the best
practices for you..........and takes away the guess work.
You'll never write your down DAL-Helper as good at that one.

I stumbled into a rather strange problem:

private void Fetch()
{
using (SqlConnection connection = new
SqlConnection(Database.ConnectionString))
{
connection.Open();
using (SqlCommand command = connection.CreateCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = @"SELECT " +
...
}
}
}
}

When this code is executed, i get the following exception on the line that
executes connection.Open():

DataPortal.Fetch failed (System.Transactions.TransactionAbortedException:
The transaction has aborted. --->
System.Transactions.TransactionPromotionException: Failure while
attempting to promote transaction. --->
System.Data.SqlClient.SqlException: There is already an open DataReader
associated with this Command which must be closed first.
at
System.Data.SqlClient.SqlInternalConnectionTds.ExecuteTransactionYukon(TransactionRequest
transactionRequest, String transactionName, IsolationLevel iso,
SqlInternalTransaction internalTransaction, Boolean
isDelegateControlRequest)
at
System.Data.SqlClient.SqlInternalConnectionTds.ExecuteTransaction(TransactionRequest
transactionRequest, String name, IsolationLevel iso,
SqlInternalTransaction internalTransaction, Boolean
isDelegateControlRequest)
at System.Data.SqlClient.SqlDelegatedTransaction.Promote()
--- End of inner exception stack trace ---
at System.Data.SqlClient.SqlDelegatedTransaction.Promote()
at
System.Transactions.TransactionStatePSPEOperation.PSPEPromote(InternalTransaction
tx)
at
System.Transactions.TransactionStateDelegatedBase.EnterState(InternalTransaction
tx)
--- End of inner exception stack trace ---
at
System.Transactions.TransactionStateAborted.CheckForFinishedTransaction(InternalTransaction
tx)
at System.Transactions.EnlistableStates.Promote(InternalTransaction tx)
at System.Transactions.Transaction.Promote()
at
System.Transactions.TransactionInterop.ConvertToOletxTransaction(Transaction
transaction)
at System.Transactions.TransactionInterop.GetExportCookie(Transaction
transaction, Byte[] whereabouts)
at System.Data.SqlClient.SqlInternalConnection.EnlistNonNull(Transaction
tx)
at System.Data.SqlClient.SqlInternalConnection.Enlist(Transaction tx)
at System.Data.SqlClient.SqlInternalConnectionTds.Activate(Transaction
transaction)
at
System.Data.ProviderBase.DbConnectionInternal.ActivateConnection(Transaction
transaction)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection
owningConnection)
at
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()

Since I explicitly create a new SqlConnection - how on earth can it be
associated with an open DataReader?

All my data access code follows the above pattern, creating the
SqlConnection in a using clause. That should kill any open DataReaders on
all connections I create, so that even if connection pooling would return
a previously used connection, it would be clean, and not associated with
any open DataReaders.

All code is run inside an ambient MSDTC transaction, as this is all part
of a BizTalk adapter.

Please advice.

Regards;
/jb

 

[Jialiang Ge [MSFT]]

Hello Jonny

It appears that the symptom that you described is almost the same as the
problem discussed on this thread:

Problem with TransactionScope and SqlDataReader with SQL Server 2005?
http://social.msdn.microsoft.com/forums/en-US/adodotnetdataproviders/thread/
5a677941-cbd6-4566-a763-bf797226bf15/
(If the long URL is truncated, please concat it manually)

Because alazela on the thread explained the problem in detail, and provided
a good workaround, please refer to his replies. If you have any other
questions or concerns, feel free to tell me.

Regards,
Jialiang Ge ([email protected], remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.

MSDN Managed Newsgroup support offering is for non-urgent issues where an
initial response from the community or a Microsoft Support Engineer within
2 business day is acceptable. Please note that each follow up response may
take approximately 2 business days as the support professional working with
you may need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations that require urgent,
real-time or phone-based interactions. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Jonny Bergdahl]

It appears that the symptom that you described is almost the same as the

problem discussed on this thread:

Almost, but not the same. Everything is actually run inside a manually
created MSDTC transaction:

using (TransactionScope ts = new TransactionScope(transaction,
TimeSpan.FromHours(1), EnterpriseServicesInteropOption.Full))

Thus there would be no need for the transaction to be promoted from a
lightweight transaction, as it is already promoted from the start.

I can however confirm that activating MARS solves the problem, however I
wonder if there is any side effects I should be aware of. The code I run
creates a message in the BizTalk message store, and also updates my
database.

Regards;
/jb

 

[Jonny Bergdahl]

CommandBehavior.CloseConnection

I would investigate that.

That shouldn't be necessary as I dispose the connection after use.

You don't show your complete code. Where are you using your IDataReader?

Sorry, the complete pattern looks like this:

using (SqlConnection connection = new
SqlConnection(Database.ConnectionString))
{
connection.Open();
using (SqlCommand command = connection.CreateCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = @"SELECT " +
.....
using (SafeDataReader dataReader = new
SafeDataReader(command.ExecuteReader()))
{
while (dataReader.Read())
{
Add(Setting.GetSetting(dataReader));
}
}
}
}

As You can see the reader is disposed together with both the command and the
connection after all rows are read (SafeDataReader is an IDataReader
encapsulation of CSLA).

http://www.knowdotnet.com/articles/schemas2.html

Yep, I've worked a bit with DotNetNuke, and that team seems to think that
returning a live IDataReader from the DAL is an excellent idea. In my world
it is the stupidest idea I've ever heard, but maybe that's just me.

I would get the EnterpriseLibrary.Data framework. They've done all the
best practices for you..........and takes away the guess work.
You'll never write your down DAL-Helper as good at that one.

I don't need any DAL helper, since ADO.NET is quite sufficient. I also use
code generation tools to avoid any nitty gritty SQL syntax errors (I'm stuck
with .NET 2.0 on this project, so no type safe LINQ calls).

Regards;
/jb

Regards;
/jb

 

[sloan]

//
Yep, I've worked a bit with DotNetNuke, and that team seems to think that
returning a live IDataReader from the DAL is an excellent idea. In my world
it is the stupidest idea I've ever heard, but maybe that's just me.//


Returning and consuming an IDataReader in the BAL is the pattern I've
adopted.
I do not like returning IDataReader's to the Presentation Layer, because the
gui developers sometimes won't close/dispose of them properly.

But a BAL developer should be smart enough to know what to do with a
IDataReader.
Get it, consume it (as fast as possible) and get rid of it.


The underlying question is
Does your DAL layer know about your BAL layer? Or does your BAL layer know
about your DAL?

I choose the "The BAL knows about my DAL layer" approach.


Pros and Cons are discussed here:
http://msdn2.microsoft.com/en-us/library/ms978496.aspx

 

[Jialiang Ge [MSFT]]

Hello Jonny

Please refer to these articles for the MARS performance and cost
considerations:

http://msdn.microsoft.com/en-us/library/ms345109.aspx
(see the MARS Performance and Cost Considerations section)

http://blogs.msdn.com/dataaccess/archive/2005/08/02/446894.aspx

Thus there would be no need for the transaction to be promoted from
a lightweight transaction, as it is already promoted from the start.

I'm looking for good ways to detect the cause in this case. I'll be back as
soon as possible.

Regards,
Jialiang Ge ([email protected], remove 'online.')
Microsoft Online Community Support

=================================================
Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

This posting is provided "AS IS" with no warranties, and confers no rights.
=================================================

 

[Jonny Bergdahl]

Does your DAL layer know about your BAL layer? Or does your BAL layer

know about your DAL?

My BAL layer actually contains the DAL code, that is the standard pattern
employed by the CSLA framework.

Regards;
/jb

 

[Jonny Bergdahl]

I'm looking for good ways to detect the cause in this case. I'll be back

as
soon as possible.

Any progress on this?

The problem only occurs when run under BizTalk (ie; when using an MSDTC
transaction), so I am uncertain on how to best debug or trace this issue.

Regards;
/jb

 

[Jialiang Ge [MSFT]]

Hello Jonny

The problem only occurs when run under BizTalk

Do you mean that the code runs well when it's under Winforms or ordinary
console applications? Is it possible that the permission of Biztalk causes
the problem? I'm not familiar with Biztalk. A Biztalk engineer in my team
suggests trying to configure Biztalk to run as the same user account as it
is in console or winform test apps. If this eliminates the problem, we can
narrow the issue down to the Biztalk account.

Regards,
Jialiang Ge ([email protected], remove 'online.')
Microsoft Online Community Support

=================================================
Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

This posting is provided "AS IS" with no warranties, and confers no rights.
=================================================

 

[Jonny Bergdahl]

Do you mean that the code runs well when it's under Winforms or ordinary

console applications? Is it possible that the permission of Biztalk causes

The code runs as expected in unit tests, but it is not running exactly the
same code as when run under BizTalk. BizTalk runs under the user id of one
of my peers, and we have the same user rights in the SQL Server.

Is there some kind of tracing I can enable, in order to track down what
"DataReader" causes the problem?

Regards;
/jb

 

[Jialiang Ge [MSFT]]

Hello Jonny

Given a SqlConnection object, there is a cool method to reverse back and
find the live DataReader that is attached to the connection object:

The idea is to use the FindLiveReader method of the SqlInternalConnection
class. FindLiveReader returns a SqlDataReader if there is one,
Nothing/null otherwise.
Both SqlInternalConnection and FindLiveReader are internal, so you need to
access them via Reflection. I write the following sample code for your
references:

Type connType = conn.GetType();
object internalConn = connType.InvokeMember("GetOpenConnection",
BindingFlags.InvokeMethod | BindingFlags.Instance |
BindingFlags.NonPublic,
null, conn, null);
Type internalConnType = internalConn.GetType();
SqlDataReader reader = internalConnType.InvokeMember("FindLiveReader",
BindingFlags.InvokeMethod | BindingFlags.Instance |
BindingFlags.NonPublic,
null, internalConn, new object[] { null }) as SqlDataReader;
if (reader != null)
{
Type readerType = typeof(SqlDataReader);
SqlCommand cmd = readerType.InvokeMember("Command",
BindingFlags.GetProperty | BindingFlags.Instance |
BindingFlags.NonPublic,
null, reader, null) as SqlCommand;
Console.WriteLine(cmd.CommandText);
}

It takes a SqlConnection object (conn) as the input, and dumps the live
DataReader's SQL command if it exists.

Regards,
Jialiang Ge ([email protected], remove 'online.')
Microsoft Online Community Support

=================================================
Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

This posting is provided "AS IS" with no warranties, and confers no rights.
=================================================

 

[Jonny Bergdahl]

Given a SqlConnection object, there is a cool method to reverse back and

find the live DataReader that is attached to the connection object:

That is certainly cool!

However - the code would look like this:

using (SqlConnection connection = new
SqlConnection(Database.ConnectionString))
{
connection.Open();
<your code here>

The problem is that it is the call to connection.Open() that fails, thus
your code will never execute.

I have however investigated the problem further by using Reflector (As the
code that fails is outside my code, I was unable to use the debugger).

The inner stack trace of the exception loks like this:

System.Transactions.TransactionAbortedException: The transaction has
aborted. ---> System.Transactions.TransactionPromotionException: Failure
while attempting to promote transaction. --->
System.Data.SqlClient.SqlException: There is already an open DataReader
associated with this Command which must be closed first.
at
System.Data.SqlClient.SqlInternalConnectionTds.ExecuteTransactionYukon(TransactionRequest
transactionRequest, String transactionName, IsolationLevel iso,
SqlInternalTransaction internalTransaction, Boolean
isDelegateControlRequest)
at
System.Data.SqlClient.SqlInternalConnectionTds.ExecuteTransaction(TransactionRequest
transactionRequest, String name, IsolationLevel iso, SqlInternalTransaction
internalTransaction, Boolean isDelegateControlRequest)
at System.Data.SqlClient.SqlDelegatedTransaction.Promote()

We can see that the exception is raised in ExecuteTransactionYukon() when
trying to promote the ambient transaction to the MSDTC transaction, so I
checked what that code is doing and found this:

if ((internalTransaction != null) && internalTransaction.IsDelegated)
{
if (!this._parser.MARSOn)
{
if (internalTransaction.OpenResultsCount != 0)
{
throw SQL.CannotCompleteDelegatedTransactionWithOpenResults();
}
Monitor.Enter(session);
flag = true;
if (internalTransaction.OpenResultsCount != 0)
{
throw SQL.CannotCompleteDelegatedTransactionWithOpenResults();
}
}
else
{
session = this._parser.GetSession(this);
flag2 = true;
}

Here it checks for a delegated transaction (which should be my MSDTC
transaction), and then proceeds to check that transaction for open results.
This means that another, already opened, SqlConnection in the same MSDTC
transaction scope seems to have an open DataReader (or at least a pending
dataset) waiting.

As I have been unable to debug I don't know the concrete type of the
internalTransaction, so I have been unable to reflect further.

Is there a way (Reflection?) to query the transaction for all associated
connections, so I can use your code to get to the SQL clause of the
offending SqlConnection?

Regards;
/jb

 

[Jialiang Ge [MSFT]]

Hello Jonny

That's a nice finding. I'm looking for the methods to query the transaction
for all associated connections.

Regards,
Jialiang Ge ([email protected], remove 'online.')
Microsoft Online Community Support

=================================================
Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

This posting is provided "AS IS" with no warranties, and confers no rights.
=================================================