Re: Connections query

From: Chris (cw_at_community.nospam)
Date: 09/13/04 

Date: Mon, 13 Sep 2004 11:36:43 +0100

I have looked into this issue further and have another question. Here's
some sample code...

Dim cn As New ADODB.Connection
Dim rc As New ADODB.Recordset
Dim cmd As New ADODB.Command

    cn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security
Info=False;Initial Catalog=pubs"
    rc.Open "select au_id from titleauthor where titleauthor.royaltyper =
100", cn, adOpenStatic, adLockReadOnly

    With cmd
        .ActiveConnection = cn
        .CommandText = "byroyalty"
        .CommandType = adCmdStoredProc
        .Parameters.Append .CreateParameter(, adInteger, adParamInput, 4,
100)
        .Execute , , adExecuteNoRecords
    End With
    Set cmd = Nothing

    rc.Close
    Set rc = Nothing
    cn.Close
    Set cn = Nothing

The sample code is running two bits of code which are essentially doing the
same thing. The first way is doing it through a query built into VB, the
second is using a stored procedure. You will notice I am running the
Execute method against the command. I'm doing nothing further, i'm simply
closing the relevant items down and setting everything to nothing. Running
this bit of code opens up two connections. I've proved this is two way.
Firstly select @@connections returns +2 on my previous call to it. Secondly
I monitored this using SQL Profiler and a different SPID was used for the
stored procedure than the first recordset.

Next I tried moving the rc.Open line to below Set cmd = Nothing. This now
only uses one connection, @@connections returns +1 and only one SPID is
shown in SQL Profiler.

Firstly let me point out that I know you should open the command into a
recordset if I was running the byroyalty stored procedure. However, I am
using the Execute statement to try and find out why it opens up a second
connection. In fact if I do open up the stored procedure into a static
recordset instead of using .Execute if does this the problem (only one
connection is used), however I have to use the .Execute method because...

Many references on the web say you should use output parameters if you want
to return one single value from a database - to do this i run the execute
statement and retrieve the single value from the parameters collection. So
I now have an issue with multiple connections opening up when I run these
stored procedures if a recordset is open already. In a big invoice run I am
testing this opens up 4000+ connections which I think must be very
inefficient and need to improve this in some way.

Chris

Relevant Pages

  • Connections query
    ... Query Analyser still open I again run SELECT @@connections. ... The invoice run opens up a recordset which goes around a loop. ... TEST 1:- Instead of retrieving this value using a stored procedure I changed ...
    (microsoft.public.sqlserver.programming)
  • Re: user permissions
    ... A user should be able to kill connections only for a specific database. ... stored procedure, by doing an EXECUTE AS LOGIN and then kill any connection. ...
    (microsoft.public.sqlserver.security)
  • Re: Connections query
    ... Try to close the connection before destroying cmdTemp: ... > application opens up two connections. ... The invoice run opens up a recordset which goes around a loop. ...
    (microsoft.public.sqlserver.programming)
  • Re: TCP Connections, Bluesocket, and Mac OS X
    ... Active Internet connections ... The rest were UDP connections that had no associated state and local ... aforementioned Bluesocket policy. ... app, when launching, or performing some other task, opens up a large ...
    (alt.internet.wireless)
  • Re: TCP Connections, Bluesocket, and Mac OS X
    ... but probably just by concurrent open sessions. ... Feline O/S is not as vulnerable as windoze. ... but "show me the connections" - I'm not seeing any. ... opens up a large number of connections at a particular ...
    (alt.internet.wireless)