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

[Rob Nicholson]

I'm starting to worry a bit now. We're getting the above error when two
users hit the same database/page on an ASP.NET application using ADO.NET,
talking to a SQL 7 server. The error is perfectly repeatable :-( But this
should help!

The error is occurring inside ExecuteReader which uses a DataReader
internally.

Here are some things that I'm pretty sure it's *NOT*:

It's not because our data readers are not being closed - they are. If they
weren't being closed, then the same error would occur in single user
operation. It doesn't - it's only when two users (threads) are running at
once.

It's not connection pooling as we've turned that off using Pooling=False in
the SqlConnection connection string. I've verified it really is off by
watching connections open & close manually in SQL Enterprise manager.

The two threads are not (AFAIK) sharing the same connection object. The
SqlConnection object is not in a shared or static variable - it's stored as
a private variable within one of our classes (called MSSQL) in a variable
called m_Connection. This m_Connection variable is created each time we open
a connection using m_Connection = New SqlConnection(ConnString), opened,
used (ExecuteScalar) closed and then destroyed using m_Connection = nothing.
The instance of MSSQL is stored in the session cache but I've verified that
the two users/threads are indeed using their own instance of MSSQL and
therefore their own instance of m_Connection. The two instances of
m_Connection will have the same connection string (SQL login used, not
Windows authentication).

There are no exceptions/error conditions occuring elsewhere apart from this
final error.

I don't think it's the connection/data reader closing - I've triple checked
all open/close pairs and the close is always within a Try...Catch with the
connection closed in the Finally section.

The error never occurs in our own use of data readers, only from within
ExecuteScalar. Hmm, I guess I could write my own version of ExecuteScaler...

So, pretty flummoxed at the moment. I willing to try any suggestions!

Cheers, Rob.

 

[Scott M.]

Does it happen in EVERY circumstance? It just sounds like your connected
access is being hit twice simultaneously.

 

[S. Justin Gengo]

Are any of the objects involved being stored in Application variables?

--
Sincerely,

S. Justin Gengo, MCP
Web Developer / Programmer

www.aboutfortunate.com

"Out of chaos comes order."
Nietzsche

 

[Bruce Barker]

sql connections (until yukon), only support one request at a time. ther
error mens a second request was made on the connection without the previous
request reading all the result sets.

ExecuteScaler only reads the first row of the first result set, it does not
clear the results, you still need to close the underlying connection.

also be sure your command objects are not shared - you will get the same
results.

if you are getting this with pooling on, then you are definitely sharing the
data between thread in your code.

note: be sure you are not using fields in a vb module, as these are shared
across threads even if private. public/private just controls varible
accessibilty not sharing.

Public Module Test
private myData as myObj = new myObj ' shared across threads
End Module

-- bruce (sqlwork.com)

 

[William \(Bill\) Vaughn]

Bruce is on to something here. If you don't close the DataReader (even with
an ExecuteScalar), the pending rowset will not be flushed until you do (or
close the connection). This can take some time if the query returns a large
rowset or multiple resultsets. If you try to reuse the DataReader before
this is done, you'll get this exception.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
www.sqlreportingservices.net
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Rob Nicholson]

Does it happen in EVERY circumstance? It just sounds like your connected

access is being hit twice simultaneously.

Yes, pretty much. When the two users hit the "Go" button, it always fails at
the same line.

Cheers, Rob.

 

[Rob Nicholson]

Are any of the objects involved being stored in Application variables?

No, session used throughout.

Cheers, Rob.

 

[Rob Nicholson]

sql connections (until yukon), only support one request at a time. ther

error mens a second request was made on the connection without the previous
request reading all the result sets.

Hi Bruce,

Just to clarify this, this means one "SqlConnection" object can only handle
one request at once. I'm aware of this issue hence the reason I said I'm
pretty sure we're closing the data reader and connections each time (I've
countred the opens and closes).

ExecuteScaler only reads the first row of the first result set, it does not
clear the results, you still need to close the underlying connection.

Yes, we do that.

also be sure your command objects are not shared - you will get the same
results.

Yup, not shared - created each time within a non-shared class/object.

if you are getting this with pooling on, then you are definitely sharing the
data between thread in your code.

Nope, occurs with pooling turned off.

note: be sure you are not using fields in a vb module, as these are shared
across threads even if private. public/private just controls varible
accessibilty not sharing.

Not sure what you mean by fields?

Public Module Test
private myData as myObj = new myObj ' shared across threads
End Module

Nope, nothing like this anyway. The only thing we do have is code like this:

Readonly Property SomePointer As SomeObject
Get
Static CachedSomePointer As SomeObject
If CachedSomePointer Is Nothing Then
CachedSomePointer = GetThePointerFromSomwhere()
End If
Return CachedSomePointer
End Get
End Property

Cheers, Rob.

 

[Rob Nicholson]

Bruce is on to something here. If you don't close the DataReader (even
with

an ExecuteScalar), the pending rowset will not be flushed until you do (or

We do close the data reader I'm pretty sure. The basic problem is I think
the datareader is being closed. If it wasn't then the error would occur when
a single user tried the operation. Therefore, it looks like *somehow* the
two threads are sharing each other's connections. But I'm 99% sure they are
not sharing the same connection object.

Cheers, Rob.

 

[Rob Nicholson]

Does it happen in EVERY circumstance? It just sounds like your connected

access is being hit twice simultaneously.

I realised that I can set-up a test environment that should repeat this by
simply a loop into a page_load that simply executes 10,000 ExecuteScaler
statements:

For i As Integer = 1 To 10000
Dim NumPeople As Integer =
CIMS_App.DataServer.ExecuteScaler(SQL)
Next

The SQL being executed is Select Count(*) From People. The source for
ExecuteScaler, OpenConnnection and CloseConnection are included below. No
parameters are being passed in this example so that bit can be ignored.

Now this is where something strange happened. I ran the above code from
within VS 2003 and it crashes with "SQL Server does not exist or access
denied" error. I think I need to work out why this is happening first! Okay,
so firing off 10,000 open connections, select and close connection isn't
something that would normally happen but it shouldn't crash...

Later... if the loop is changed to this:

CIMS_App.DataServer.OpenConnection()
For i As Integer = 1 To 10000
Dim NumPeople As Integer =
CIMS_App.DataServer.ExecuteScaler(SQL)
Next
CIMS_App.DataServer.CloseConnection()

The error doesn't occur. It's the rapid opening and closing of the
connection that's crashing - SQL 7 box BTW.

Cheers, Rob.

[SqlException: SQL Server does not exist or access denied.]
System.Data.SqlClient.SqlInternalConnection.OpenAndLogin()
System.Data.SqlClient.SqlInternalConnection..ctor(SqlConnection
connection, SqlConnectionString connectionOptions)
System.Data.SqlClient.SqlConnection.Open()
Granite.MSSQL.OpenConnection() in
C:\Projects\CIMS_Dev\Granite\Databases\MSSQL.vb:379

[GException: Unable to open connection to INFORMED02]
Granite.MSSQL.OpenConnection() in
C:\Projects\CIMS_Dev\Granite\Databases\MSSQL.vb:381
Granite.MSSQL.ExecuteScaler(String SQL, SQL_Parameter[] Parameters) in
C:\Projects\CIMS_Dev\Granite\Databases\MSSQL.vb:501

[GException: Unable to load data from INFORMED02 server.]
Granite.MSSQL.ThrowOpenException(Exception ex, String SQL) in
C:\Projects\CIMS_Dev\Granite\Databases\MSSQL.vb:451
Granite.MSSQL.ExecuteScaler(String SQL, SQL_Parameter[] Parameters) in
C:\Projects\CIMS_Dev\Granite\Databases\MSSQL.vb:533
CIMS.DefaultPage.Page_Load(Object sender, EventArgs e) in
c:\inetpub\wwwroot\CIMS_Dev\Default.aspx.vb:78
System.EventHandler.Invoke(Object sender, EventArgs e) +0
System.Web.UI.Control.OnLoad(EventArgs e)
System.Web.UI.Control.LoadRecursive()
System.Web.UI.Page.ProcessRequestMain()

Here's the code:

Public Overrides Function ExecuteScaler(ByVal SQL As String, ByVal
ParamArray Parameters() As SQL_Parameter) As Object
' Trap the database operation.
Dim AlreadyOpen As Boolean
Try
' Open the connection.
AlreadyOpen = OpenConnection()
' Create SQL command.
Dim SQL_Command As New SqlCommand(SQL, m_Connection)
' Add SQL parameters.
For Each SQL_Parameter As SQL_Parameter In Parameters
Dim p As SqlParameter
Dim Value As Object = SQL_Parameter.Value
If Value.GetType Is GetType(System.Byte()) Then
Dim Data() As Byte = Value
p = New SqlParameter(SQL_Parameter.Name,
SqlDbType.Image, Data.Length, ParameterDirection.Input, False, 0, 0,
Nothing, DataRowVersion.Current, Data)
Else
Throw New Exception("MSSQL.ExecuteScalar can't handle "
& Value.GetType.FullName & " data types!")
End If
SQL_Command.Parameters.Add(p)
Next
' Add transaction if enabled.
If Not m_Trans Is Nothing Then
SQL_Command.Transaction = m_Trans
End If
' Execute the command.
ExecuteScaler = SQL_Command.ExecuteScalar
Catch ex As Exception
ThrowOpenException(ex, SQL)
Finally
' Close the connection.
If Not AlreadyOpen Then
CloseConnection()
End If
End Try
End Function

Public Overrides Function OpenConnection() As Boolean
If m_Connection Is Nothing Then
m_Connection = New SqlConnection(ConnectionString)
End If
If m_Connection.State = ConnectionState.Closed Then
Try
m_Connection.Open()
Catch ex As Exception
Throw New GException("Unable to open connection to " &
m_DataLink.ServerName, GraniteErrors.Error0016, ex)
End Try
Else
OpenConnection = True
End If
End Function

Public Overrides Sub CloseConnection()
If Not m_Connection Is Nothing Then
m_Connection.Close()
m_Connection = Nothing
End If
End Sub

 

[Rob Nicholson]

The error doesn't occur. It's the rapid opening and closing of the

connection that's crashing - SQL 7 box BTW.

Later...

It manages to open and close about 3,800 times before crashing out

Cheers, Rob.

 

[Rob Nicholson]

within VS 2003 and it crashes with "SQL Server does not exist or access

denied" error. I think I need to work out why this is happening first!

Okay,

Later...

Okay, we can ignore this error - see following KB article:

http://support.microsoft.com/default.aspx/kb/328476

Basically, if you don't use pooling (which is turned off at the moment for
debug), then opening and closing ~4,000 connections rapidly (sockets) causes
SQL Server to fall over. This is exactly what's happening. As I said,
normally you wouldn't do something like this but I'm trying to diagnose the
main problem with datareader.

Cheers, Rob.

 

[S. Justin Gengo]

Rob,

If you're caching connection objects that could be the same as placing the
object into an application level variable. Try creating new objects one at a
time instead of caching them and then testing. I believe that eventually one
changing one of the cached objects to a new one will solve the problem.

--
Sincerely,

S. Justin Gengo, MCP
Web Developer / Programmer

www.aboutfortunate.com

"Out of chaos comes order."
Nietzsche

 

[Rob Nicholson]

If you're caching connection objects that could be the same as placing the

object into an application level variable. Try creating new objects one at

a

I'm beginning to suspect the fault lies in another of our developers code as
in the tests I mention above, I did this:

Begin
Open connection
Fire off 10,000 ExecuteScaler operations
Close connection
End

I then ran the above on four PCs at once, all executing the above code at
the same time on the same web server and that works flawlessly. This suggest
that they are not using the same connection object.

Actually, the current fault does occur in the same programmers code
everytime...

Cheers, Rob.

 

[Rob Nicholson]

I'm beginning to suspect the fault lies in another of our developers code
as

in the tests I mention above, I did this:

This bug hunt is like a detective story My suspicion that the problem
does like with the two threads attempting to use the same variables when
they shouldn't be...

The MSSQL object has a variable called m_Connection which holds the
SqlConnection object. It's created like this in OpenConnection:

If m_Connection Is Nothing Then
m_Connection = New SqlConnection(ConnectionString)
End If

When the program is run with one user, m_Connection is always nothing and
therefore creates the connection everytime.

However, when the program is run with two users, m_Connection sometimes
isn't always nothing so a simple trap like this fires:

If Not m_Connection Is Nothing Then Stop

This is a the cause of the error - the two threads ARE trying to re-use same
connection object hence the OpenReader problem.

The big question is WHY this is happening as it shouldn't... The
m_Connection object is in effect stored in the session cache. Each user
*should* have their own session cache and therefore own m_Connection.

Later

Cheers, Rob.

 

[Rob Nicholson]

also be sure your command objects are not shared - you will get the same

results.

if you are getting this with pooling on, then you are definitely sharing

the

FOUND IT!! One of the other developers on the team had used a Private Shared
variable in a class which is a BIG no-no in ASP.NET land or in any
multi-threaded application. Therefore, User A and User B were sharing the
same connection variable - well actually they were sharing a higher level
object holding the connection so net result is the same.

Heads will roll I've spent most of my Saturday trying to track this down :-(

Thanks all for the suggestions.

Cheers, Rob.