Connections query

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

Date: Thu, 9 Sep 2004 16:42:13 +0100

Scenario... I open up Query Analyser and run SELECT @@connections. This
return 1 (let's assume the counter was at zero before I started). This
makes sense because Query Analyser has an open connection. I then run my VB
6 application which connects to SQL Server using ADO through OLE DB. My
application opens up two connections (each to a different database). With
Query Analyser still open I again run SELECT @@connections. This now
returns 3, as expected.

Next I start a long bit of complicated code which involves the production of
invoices. The invoice run opens up a recordset which goes around a loop.
Within this loop I open up another recordset. Because I know a forward only
recordset will open up an implicit second connection when both recordsets
are open concurrently, I decide to change them to static.

So i've now got two recordsets open, going around a large loop. Running
SELECT @@connections again returns a value of 3, as expected. I then try to
return a value from my database using a stored procedure...

Dim cmdTemp As New ADODB.Command

    With cmdTemp
        .ActiveConnection = gDBConn.cn
        .CommandText = "GetPreviousInvoiceAmount"
        .CommandType = adCmdStoredProc
        .Parameters.Append .CreateParameter(, adInteger, adParamInput, 4,
PreviousInvoiceID)
        .Parameters.Append .CreateParameter("InvoiceAmount", adCurrency,
adParamOutput, 8)
        .Execute
        PreviousInvoiceAmount = .Parameters("InvoiceAmount").Value
    End With
    Set cmdTemp = Nothing

When I run the above stored procedure and then execute SELECT @@connections
it now reads 4. My question is why - Why has a new connection been created?

So I tried to break the problem does to find out why - I ran two tests...
TEST 1:- Instead of retrieving this value using a stored procedure I changed
it to a built-in SQL command using adCmdText and opened up a recordset.
This method does not open up a new connection, so why does a stored
procedure? And is there anything I can do about this? TEST 2:- If I run
this stored procedure on its own (i.e. Not within my big invoice production)
it does not open up a new connection so it must be a combination of factors.

I've tested an invoice run of 400+ invoices. At the end of the invoice run,
SELECT @@connections returns over 400, meaning that is has opened up more
than 400 connections, with the stored procedure causing this large value.

Finally, just to confuse the matter... at the end I decided to run another
test. Using Performance monitor built into Windows, I created a counter for
the SQL General Statistics: User Connections option. Before I click my
produce invoice button the counter shows 3 connections, as expected. When
the invoice run is in operation the counter constantly fluctuates between 3
and 4 connections.

Any insight would be very much appreciated.

Thanks
Chris

Loading