Question:
I have an MTS object that contains an ADO connection object which I use in an ASP page. I look at the MTS console and confirm that the package gets activated, and I use ODBC performance-monitoring counters to verifty a connection gets pooled. But when I manually shutdown the component or let it idle timeout, I notice the connection gets closed also. Shouldn’t the ADO connection object stay around after the MTS component gets destroyed, since the ODBC idle connection time is higher than the component idle time?
Answer:
If the ADO object lasts longer than the ODBC connection, that means you’ve dropped your references to the object by shutting down your component. MTS cleans up its garbage based on reference count and that particular connection would now have a zero reference count.
One method I use for handling this situation is to create an object that, when it spins up, creates a connection to the database. When it shuts down, it closes the connection. Other objects are able to use it to get access to the database (I exported an Execute method), and it sends back disconnected ADO recordsets. This accomplishes the same task. From other things and books I’ve read, the jury is still out on this one, but one Microsoft article I read mentioned this particular method as one that would work to manage your limited number of database connections.
