ADO holding SQL Connection Open, WHY?

Question

ADO holding SQL Connection Open, WHY?

ccundy

SSCommitted

Points: 1913
More actions
January 26, 2004 at 11:42 am

#161098

I have the following VB code that I believe should close the connection to the database but doesn't seem to. I am attempting to use a disconnected recordset. When I query the sysprocesses table it retains a connection until I set conn = Nothing, which I don't really want to do every time I access data. I do not understand why this is happening. Isn't that what the conn.Close should be doing? Closing the connection. Querying the sysprocesses table is what show the connections, correct?
This is how I can tell it is holding the connection
Select * From master..sysprocesses Where dbid= 9
    Dim conn As ADODB.Connection
    Set conn = New ADODB.Connection
    Set rstLocations = New ADODB.Recordset
    conn.Open connString
    rstLocations.CursorLocation = adUseClient
    rstLocations.Open "Select * From Locations", _
         conn, _
         ADODB.adOpenForwardOnly, _
         ADODB.adLockBatchOptimistic
    Set rstLocations.ActiveConnection = Nothing
    conn.Close
Can someone either tell me what I am doing wrong or explain to me why this is happening?
Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135368 More actions · Answer 1

connection pooling

this will keep the connection alive for a while (default 60sec ?) because apparently building a connection taks a lot of resources.

Unless you use application roles this will not hinder.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

rschaeferhig SSCrazy Eights Points: 8776 More actions · Answer 2

Set rstLocations.ActiveConnection = Nothing

conn.Close

I believe you have to reverse the above statements. You close the connection THEN destroy it. I'm not sure what the effect is of trying to destroy an open connection...

conn.close

Set conn = Nothing

jaredsimi SSCrazy Points: 2420 More actions · Answer 3

You didn't supply the connectstring to your example. However, to disable connection pooling add the following to your connection string.

OLE DB Services = -2

there are various articles in MSDN that describe connection pooling

and the registry values that control it.

Developer22 Valued Member Points: 65 More actions · Answer 4

You don't want to turn off connection pooling! Unless there is a very very good reason.

By default, ADO will keep ( I think 6 ) connections in a pool for a short period of time. These connections stay alive in SQL server, but VB sees them as closed.

Keep in mind, connection pooling only works when the connectionstring is the same string each time. If you are using a different connectionstring for every connection, then you would want to disable connection pooling or revisit the reason for having different connection strings.

Hope this helps.

ccundy SSCommitted Points: 1913 More actions · Answer 5

this will keep the connection alive for a while (default 60sec ?) because apparently building a connection taks a lot of resources.

Hey, alzdba, you are exacty right, after about 60 seconds the connection died. This is what I was expecting.

Does anyone know of any sites or articles where I can get more information on ADO and connection pooling with VB.

Thanks

What is better, to keep disconnected recordsets or keep a live connection. I was always under the impression that disconnecting was better, so you would not keep resources open that were not being used. I suppose it depends on the situation.

What do you think?

Developer22 Valued Member Points: 65 More actions · Answer 6

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnmdac/html/pooling2.asp

Johan Bijnens SSC Guru Points: 135368 More actions · Answer 7

As always : it depends.

.Net will definitely go for disconnected data with closed connections. Because of connection pooling, the overhead for having a "new" connection is minimised.

Same goes for others. Only keep your connection open if you need to.