madhav102398
asked on
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,
Madhav.
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,
Madhav.
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
conn1.open('...DB1...')
rsDB1 = conn1 .open(...)
conn2 = new ADODB.connection
conn2.open('...DB2...')
rsDB2 = conn2 .open(...)
conn1 = new ADODB.connection
conn1.open('...DB1...')
rsDB1 = conn1 .open(...)
conn2 = new ADODB.connection
conn2.open('...DB2...')
rsDB2 = conn2 .open(...)
ASKER
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
conn1.open('...DB1...')
rsDB1 = conn1 .open(...)
conn2 = new ADODB.connection
conn2.open('...DB2...')
rsDB2 = conn2 .open(...)
'**** now close the second connection
conn2.Close
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.
Yes, I am doing with two seperate connection objects. So, trying to continue the sample code you have written above.
conn1 = new ADODB.connection
conn1.open('...DB1...')
rsDB1 = conn1 .open(...)
conn2 = new ADODB.connection
conn2.open('...DB2...')
rsDB2 = conn2 .open(...)
'**** now close the second connection
conn2.Close
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...
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconConnectionPoolingForSQLServerNETDataProvider.asp
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.
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...
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconConnectionPoolingForSQLServerNETDataProvider.asp
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.
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I have tried
1. To set the HKEY_LOCAL_MACHINE\SOFTWAR
2. Tried to use SQLSetEnvAttr from odbc32.dll to explicitly call and set the pooling off.
Const SQL_ATTR_CONNECTION_POOLIN
Const SQL_CP_ONE_PER_DRIVER = 1
Const SQL_IS_INTEGER = -6
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_ATTR_CONNECTION_POOLIN
SQL_CP_OFF, _
SQL_IS_INTEGER)
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.
1. To set the HKEY_LOCAL_MACHINE\SOFTWAR
2. Tried to use SQLSetEnvAttr from odbc32.dll to explicitly call and set the pooling off.
Const SQL_ATTR_CONNECTION_POOLIN
Const SQL_CP_ONE_PER_DRIVER = 1
Const SQL_IS_INTEGER = -6
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_ATTR_CONNECTION_POOLIN
SQL_CP_OFF, _
SQL_IS_INTEGER)
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.
ASKER
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.
http://support.microsoft.com/default.aspx?scid=kb;EN-US;228843
Thanks to all.
You can find this in article Q228843 in msdn.
http://support.microsoft.com/default.aspx?scid=kb;EN-US;228843
Thanks to all.
ASKER
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.
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.
dfiala13
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.
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. ;)
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. ;)
ASKER
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.
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.
ASKER