Ado Connection not closed

Hello all,

I am using VB with ADO to connect to different databases in my SQLServer 2000.

1. First I open a database say, DB1, using ADO and it is not closed.
2. Second I open another database say, DB2, again using ADO & then close the ADO connection and set the connection object to nothing.

But then i notice in the enterprise manager that the SQLServer has a process still left open. And i need to detach the second database after i am done working on it. I actually take some data out of the first database and import it into the second database.

I cannot detach the second database, because the process created with the open connection operation still stays in the sqlserver processes.

But, If I close the open & close first connection before opening the second connection things are fine. But i need to keep the first connection open. So please help with information on why the second connection's process is still left open.

Thanks in advance,
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

dfiala13Connect With a Mentor Commented:
ADO does do connection pooling, as of version 2.0 I believe.  Here's some info more likely to be of use to you...

Looks like you might have to do some registry work to manage its behavior.
madhav102398Author Commented:
Please help ... i need this urgent. Thanks in advance.
Each connection should be to exaclty one DB at a time so use two separate connection objects. Each connection has a server-sdie part (which is one reason you should always close them asap).

conn1 = new ADODB.connection'...DB1...')
rsDB1 = conn1 .open(...)

conn2 = new ADODB.connection'...DB2...')
rsDB2 = conn2 .open(...)
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

madhav102398Author Commented:
Thanks for the response monosodiumg.

Yes, I am doing with two seperate connection objects. So, trying to continue the sample code you have written above.

conn1 = new ADODB.connection'...DB1...')
rsDB1 = conn1 .open(...)

conn2 = new ADODB.connection'...DB2...')
rsDB2 = conn2 .open(...)

'**** now close the second connection
Set conn2 = Nothing

At this point, even though my second connection is closed, when i look in current processes in the SQLServer2000 enterprise manager(I did refresh). I still see a process associated with the DB2 even though we just closed it above. And this i notice ONLY because the first connection is LEFT OPEN before opening the second connection. I mean, if the first connection was closed before opening the second one things are fine. But, unfortunately i do need to keep the first connection open.

Thanks again. Please advice.
ADO.NET runs connection pooling under the covers.  What you are creating are not the physical connections, but requests to use a physical connection.  If the pool has one available when you open a connection, it assigns a physical connection to the connection object you have created, if not it creates a new connection and assigns it to your connection object.

When you close the connection object, the physical conenction is not closed but put back in the pool, for use by another connection object.  This is done for performance reasons, as it is expensive to constantly open and close physical connections.

See here for more details...

The physical connection can be closed when return to the pool if you set the Connection Lifetime argument in your connection string to a small value.  Then should be removed.  See the above link for more details.
madhav102398Author Commented:
Sorry .. am a junior in this area ...

i am not dealing in anyway with .NET. My program is simply dealing with VB & SqlServer2000 & ADO (MDAC 2.1).

So dfiala13, does your comment still has any advice for me?

And i am not sure if by default any pooling is enabled?  Please advice ... thanks for your suggessions.
madhav102398Author Commented:
I have tried

1. To set the HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\SQL Server\CPTimeOut to 0 (zero), which did not help even after restart of machine, thinking that this value has to be loaded.

2. Tried to use SQLSetEnvAttr from odbc32.dll to explicitly call and set the pooling off.
Const SQL_CP_OFF = 0
Private Declare Function SQLSetEnvAttr Lib "odbc32.dll" ( _
                    ByVal EnvironmentHandle As Long, _
                    ByVal EnvAttribute As Long, _
                    ByVal ValuePtr As Long, _
                    ByVal StringLength As Long) As Integer

*-************ function call ******
   Call SQLSetEnvAttr(0&, _
                SQL_CP_OFF, _

But it does not help also. I have also tried with 2 different SQLServers on different machine. The first & second connections for sqlServers on different machines. I am not sure what i am dealing with, who is pooling what here? It is not SQLServer because i am trying with SqlServers on different machine's.

Please advice. Thanks dfiala13 for all the suggessions.

Please help.
madhav102398Author Commented:
OK Guys .. i think i found the reason why the SQLServer has a process/connection still open. This is because of the "OLEDB Session Pooling" and right now i am researching on how to turn OFF this thing, because by default this is ON.

You can find this in article Q228843 in msdn.;EN-US;228843

Thanks to all.
madhav102398Author Commented:
And just for your information, we can control it via the Connection String using "OLE DB Services=-2"

Thanks you all for your time again.
Glad you figured out how to turn off the pool.
Hmm.... Guess no one even pointed him in the right direction.  Go ahead and close it.
a)identified the critical issue (connection pooling) and
b)provided a link to the relevant MS page.

All madhav had to do after that was RTM.

madhav states in the refund request:
>I answered my own question ...
Sure. After he had been led to spot marked X, he dug the hole and found the treasure. The hard part was getting to the spot marked X.

>... & posted the answer also
Again, the value of this question as a PAQ resudes inthe identofication of connection pooling as the critical issue. The fine detail of the solution varies withthe speicific versions of the software involved.

Were I the madhav, I would feel satisifed with that, at least at the B level.

I think it would be grossly unfair to dfiala13 to deny him the points.

Thanks for the support monosodiummg.

I am happy to chalk it up to live and learn --ie, who to answer questions for and who to let answer their own questions in the future. ;)
madhav102398Author Commented:
hey guys .. come on... i dont want to be rude here or something ...

after dfiala13  wrote "Glad you figured out how to turn off the pool." ... and i was just wrapping up with my code for release .. i was just jubiliant and well merry too ... i struggled for a day on this stupid issue....

mainly i was too always thinking about pooling .. but i was always looking at ODBC pooling in MSDN too ... and never wondered about the "session pooling" ...

well .. i might have being merry  ... just patted my self & said .. i did it .. again am junior ;-))))  .... so it was a big thing for me ... u can imagine when u r a junior right?

anywayz ... i am accepting dfiala13 aswer ...

thanks for all your support again.
All Courses

From novice to tech pro — start learning today.