Re: Limiting session pool size

I can't figure out why all of a sudden connection 2 appears, when
connection
1 is available. Each method calls the same connect function, so the
connection string and process is the same for each. Any ideas?

Yes.
You are aware that at any given time for a Server-Sided, ForwardOnly
recordset, you can only have 1 of them per Connection for SQL Server?

If you had 1 Active Connection, it is possible to 2+ Server-sided Keyset
Recordsets, say, nested as well (That is while Recordset 1 is active, you
create and open Recordset 2). Now you cannot do that with ForwardOnly
Cursors. They can be serial in that you open, read, close Recordset 1 and
then open, read, close Recordset 2. But in this case, AT ANY GIVEN TIME,
there is only 1 active Server-sided ForwardOnly Cursor for the Connection
object. That is part of the design of SQL Server, not a provider limitation.

Now what happens if try to open a 2nd Server-Sided, ForwardOnly recordset
when you have an existing Server-Sided, ForwardOnly recordset active?
My guess is that a 2nd Connection object is duplicated from the 1st
Connection object and the 2nd Server-Sided, ForwardOnly RecordSet is
activated from the 2nd Connection object. That is why you will see 2nd
Connection.

And if you have 5 active Server-Sided, ForwardOnly recordsets then they will
be attached to 5 different Connection objects.

This may be an explanation for what you are seeing.

Of course this scenario could play havoc with multiple threads

See
http://www.sql-server-performance.com/sk_connection_pooling_myths.asp
and move down to
"Question 5: True or False: Opening multiple recordsets on the same
connection object will insure you take a connection from the pool."
and read subsequent paragraphs
and a talk as to whether this is a real problem and what you can do about
it.

I use ForwardOnly cursors a lot - they are the fastest cursor (but also the
most limited) - mainly to build small lookup tables in C++.
But I build each lookup table in turn so this limitation is not a problem
for me.

Hope that helps

Stephen Howe


.

Relevant Pages

  • Re: Another Drop List question
    ... :>>Dim sConnString, connection, sSQL ... :> to make sure the sql statement contains what you expect: ... Dim rsName 'Holds the recordset for the records in the database ... 'Set an active connection to the Connection object using a DSN-less ...
    (microsoft.public.inetserver.asp.general)
  • Re: I am having a problem in my asp in the recorsets running at iis 6.0 and sql 2000. Sometimes it
    ... > and if I do the same sql querys using query analizer, the sql server ... maintaining your code know what each recordset contains? ... Always use an explicit connection object. ...
    (microsoft.public.inetserver.asp.general)
  • Re: not in list problem
    ... the above code is part of what it runs when the application opens. ... how can i use a global connection object. ... > connection string to open a recordset, ...
    (microsoft.public.access.formscoding)
  • Re: not in list problem
    ... > make it work was with the connection object. ... >> are using one global connection while Access is using a different one may ... >>>> connection string to open a recordset, ...
    (microsoft.public.access.formscoding)
  • Re: What to do when you cant retrieve content from a DB
    ... when the SQL server goes down or the sites lose their ... SQL connection strings for pages are available from both web.config ... allowing you to close your recordset and connection immediately before ...
    (microsoft.public.inetserver.asp.db)