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

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.Does it happen in EVERY circumstance? It just sounds like your connected
access is being hit twice simultaneously.
"Rob Nicholson" <informed@.community.nospam> wrote in message
news:uz%23T7dGwFHA.2132@.TK2MSFTNGP15.phx.gbl...
> 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.
>
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
"Rob Nicholson" <informed@.community.nospam> wrote in message
news:uz%23T7dGwFHA.2132@.TK2MSFTNGP15.phx.gbl...
> 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.
>
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)
"Rob Nicholson" <informed@.community.nospam> wrote in message
news:uz%23T7dGwFHA.2132@.TK2MSFTNGP15.phx.gbl...
> 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.
>
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.
__________________________________
"Bruce Barker" <brubar_nospamplease_@.safeco.com> wrote in message
news:u1SomHHwFHA.2232@.TK2MSFTNGP11.phx.gbl...
> 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)
>
> "Rob Nicholson" <informed@.community.nospam> wrote in message
> news:uz%23T7dGwFHA.2132@.TK2MSFTNGP15.phx.gbl...
>
> 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.
> Are any of the objects involved being stored in Application variables?
No, session used throughout.
Cheers, Rob.
> 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.
> 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.
> 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\M
SSQL.vb:379
[GException: Unable to open connection to INFORMED02]
Granite.MSSQL.OpenConnection() in
C:\Projects\CIMS_Dev\Granite\Databases\M
SSQL.vb:381
Granite.MSSQL.ExecuteScaler(String SQL, SQL_Parameter[] Parameters) in
C:\Projects\CIMS_Dev\Granite\Databases\M
SSQL.vb:501
[GException: Unable to load data from INFORMED02 server.]
Granite.MSSQL.ThrowOpenException(Exception ex, String SQL) in
C:\Projects\CIMS_Dev\Granite\Databases\M
SSQL.vb:451
Granite.MSSQL.ExecuteScaler(String SQL, SQL_Parameter[] Parameters) in
C:\Projects\CIMS_Dev\Granite\Databases\M
SSQL.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