Re: ADO Connection.Execute Method leaves Open SQL Process

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance


"Joe Cool" <joecool1969@xxxxxxxx> wrote in message
news:b8d8dde0-f1f1-49d3-b2cb-96dfdd073b0b@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Jul 29, 9:56 am, "Ralph" <nt_consultin...@xxxxxxxxx> wrote:
"Joe Cool" <joecool1...@xxxxxxxx> wrote in message

Definitely a multi-user app with multple boxes. What makes it
sound otherwise?

ha, your first paragraph where it talks about "a VB6 app".

but ... nvm as we are likely going into other waters... lol


Have the App create a single ADODB.Connection object (New),
then .Open and .Close it on demand, but do NOT destroy it
(Set to Nothing) until the App exits. This will insure the App is
requesting only one connection from a single ADO Connection
Pool, thus if all requests are sequenced and not over-lapping
- from the App's point of view only one connection will be ever
be requested from the Pool.

That's a very good suggestion but unfortunately would require
some major work as to the complexity of this app and my
boss is not likely to approve such a major overhaul at this time.

Then you may be screwed. <smile>

The best way to manage connections with ADO is to use the Connection Pool
and thus your first objective has to be to insure that each App uses the
Connection Pool in the most optimal manner. Period.

The connection pool is created when you create an ADODB.Connection object.
You get a pool and one connection from the pool. As long as your App makes
requests in an orderly fashion - one after another without 'over-lap'
(something in a loop, or mulitple calls to Execute in the same routine),
then the App will be able to use a single 'ADO connection' through-out its
lifetime.

Also note that an ADO Connection (not the object) is only open while it is
being used - ado connections close after 2secs to 2 minutes of inactivity
anyway. Telling the connection object to close a connection is allowing the
object to close it sooner.

Creating an destroying an ADODB.Connection adds nothing to the solution but
additional overhead.

Even after you insure this - there is likely something else going on that is
the real cause and you will have to modify your code to adapt to it.


However, SQL Server itself often creates additional "connections" to
satisfy
a request. In which case there may be little to nothing you can do about
it
from the App's side of things.

Also, if not using the return, use the adExecuteNoRecords option.

Tried that, didn't help.


It might be useful to see your connection string and attributes, and a
sample query

Typical code:

Dim cn as Connection

With cn
.Provider = "MSDASQL"
.ConectionString = "User Id=username;Password-password;Data
Source=dsnname;"
.Open
End With

' single stepping through the code and checking the SQL Activity
Monitor shows no new process created at this point!!

cn.Execute "INSERT INTO tablename ( columna ) VALUES
( 'somevalue' )", , adExecuteNoRecords

' at this point a new SQL process HAS been created!!

cn.Close
Set cn = Nothing

' SQL Process STILL exists!!

==============================================
==============================================

That is easy enough to fix.
Just create a single global reference to one ADOD.Connection object.
Run that "With cn" block once at the start of your program, then Set to
Nothing at the end. Keep the .Open - to test the connection then .Close it.
In your code before you make any connection request do a ...
cn.Open
.... ' do your stuff, then ...
cn.Close
Delete all occurances of "Set cn = Nothing".

Why? Again because after you have done that we know each App is using only
one ADO Connection. If your app is not deliberating requesting multiple
connections - then it is not ADO that is the problem.

Note: Setting a cn object to Nothing, while usually is quite deterministic
from a practical view, is not absolutely going to destroy everything at that
very sec. As ADO runs outProc - there can be an delay on a busy system,
though that is usually an exotic case.

Your real problem is most likely because SQL Server is opening up additional
implicit connections (not pooled) and holding them open - because it is
receiving multiple pending requests. Changing the Queries to SPs that don't
produce implicit default recordsets may help.

On an extremely busy system, reworking the Apps to queue messages to an
external application which does the actual communication with SQL Server may
also help to cutdown the number of actual connections.



.

Relevant Pages

  • Re: Connection pooling
    ... Each message spawns a new instance of a C# dll via COM. ... | I have only tested the dll's being created via a Console app, ... | use that pool. ... transaction, you close the connection. ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Internal connection fatal error
    ... It's just the nature of the app... ... > 80 connections in the pool tells me this is a heavily loaded system. ... > Yes, if an operation fails at the server, the connection can be broken. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: A month with a Nokia n95 (long)
    ... and can't play. ... One of these days I will get around to writing a small app to do it all on the fly. ... HSDPA works well and you can have more than one internet connection going at once. ... For example I can use it as a modem for my laptop and also "listen again" to a Beeb radio programme. ...
    (uk.telecom.mobile)
  • Re: too many cursors open
    ... Is the app java? ... This usually means you are not closing callable statements and result ... instrument it and instrument where you get the connection from the pool ...
    (comp.databases.oracle.misc)
  • Re: ADO Connection.Execute Method leaves Open SQL Process
    ... your first paragraph where it talks about "a VB6 app". ... requesting only one connection from a single ADO Connection ... ' at this point a new SQL process HAS been created!! ...
    (microsoft.public.vb.database.ado)