Re: Connections query

From: Ilya Margolin (ilya_no_spam__at_unapen.com)
Date: 09/09/04 

Date: Thu, 9 Sep 2004 16:43:06 -0400

Chris,

Try to close the connection before destroying cmdTemp:

 .ActiveConnection.Close

Ilya

"Chris" <cw@community.nospam> wrote in message
news:Ose7ROolEHA.1652@TK2MSFTNGP09.phx.gbl...
> 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