VB6 ADO Connection Pooling

Question

We have a bunch of VB6 apps at our company. We are trying to debug a random SQL timeout error and did a trace with SQL Server Profiler on the Audit Login event. We noticed that the connections were coming in as nonpooled. We use the SQLOLEDB provider with SQL Server 2000 & 2005. I searched the internet and everything I come across says that connections are pooled by default in the SQLOLEDB provider, but we are not seeing this. Below is the code we use to connect to the database. We really need to have these connections pooled because we think this may be the problem with our random timeout error. Could any one shine some light on why connection pooling isn't working and any way to make it work? Thanks.

Dim cnn As New ADODB.Connection
cnn.ConnectionString = "Provider=SQLOLEDB;Data Source=xxx;Catalog=xxx;User ID=xxx Password=xxx;"
Call cnn.Open
Dim cmd As New ADODB.Command
Set cmd.ActiveConnection = cnn
cmd.CommandText = "SELECT * FROM [Table]"
Dim rs As New ADODB.RecordSet
Call rs.Open(cmd, , adOpenStatic, adLockOptimistic)
While Not rs.eof
    'Do stuff
    Call rs.MoveNext
Wend
'Close and Dispose connection here

Answer 1

Disposing the connection on every call could prevent pooling

...at least one instance of a Connection object instantiated for each unique user—at all times. Otherwise, the pool will be destroyed when the last Connection object for that string is closed.

http://msdn.microsoft.com/en-us/library/ms810829.aspx

Answer 2

Thanks for the MSDN article. I've tried everything they have in there. I left a connection open, and looped through creating 100 more but none of them get pooled.

Answer 3

See if this article sheds any light on the subject

Answer 4

I messed around and opened a connection at app startup and kept it open through the entire time the app was running. Connection pooling did start after the second opened and closed connection.

Answer 5

You mentioned that you were trying to track down a random timeout problem. I've had the same, generally when I was doing a SELECT that returned a lot of rows. Two things:

Cnn.CursorLocation=ADODB.adUseServer

(The other option is adUseClient) - I believe adUseServer gave me faster queries, which reduced the likelyhood of timeouts. You do this before you open the connection, I believe.

Cnn.CommandTimeout=0

Also before the open(), tells it that you want an infinite timeout. I think the default timeout is something like 30s, which was way too short for some queries. The CommandTimeout will be used for Recordset queries. If you use a Command object, it has it's own CommandTimeout member, which doesn't appear to inherit from the Connection (ie, I set it before I Execute a command).

Sorry if the syntax isn't quite right, I'm cutting from some C++ code.