Combining VB6 ADO Connections

Question

Our company has many VB6 applications. We are trying to debug a random SQL timeout error and traced using the SQL Server Profiler in the login event. We noticed that connections do the same as unused ones. We are using the SQLOLEDB provider with SQL Server 2000 and 2005. I searched the Internet, and everything I see suggests that joins are merged by default with the SQLOLEDB provider, but we don’t see this. Below is the code we use to connect to the database. We really need to combine these connections because we think this may be a problem with our random timeout error. Can someone enlighten why the connection is not working and somehow 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

Disconnecting with each call may interfere with the call

... at least one connection instance is an object created for each unique user at all times. Otherwise, the pool will be destroyed when the last Connection Object for this row is closed.

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

Answer 2

I got confused and opened the connection when the application started and continued to open it for the entire time the application was running. The connection pool started after the second open and closed connection.

Answer 3

You mentioned that you were trying to track down a random timeout problem. I had the same thing, as a rule, when I did SELECT, which returned many rows. Two things:

Cnn.CursorLocation = ADODB.adUseServer

(Another option is adUseClient). I believe adUseServer gave me faster requests, which reduced the chance of timeouts. You do this before you open the connection, I suppose.

Cnn.CommandTimeout = 0

Also before open (), it says that you want an infinite timeout. I think the default timeout is something like 30 seconds, which is too short for some queries. CommandTimeout will be used for Recordset queries. If you use a Command object, it has its own CommandTimeout member, which does not seem to inherit from Connection (i.e. I installed it before executing the command).

Sorry if the syntax is not quite right, I cut from some C ++ code.