Solved

Ado Connection not closed

Posted on 2004-04-06
15
567 Views
Last Modified: 2009-12-16
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.
0
Comment
Question by:madhav102398
  • 7
  • 5
  • 2
15 Comments
 

Author Comment

by:madhav102398
ID: 10766559
Please help ... i need this urgent. Thanks in advance.
0
 
LVL 12

Expert Comment

by:monosodiumg
ID: 10766742
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(...)
0
 

Author Comment

by:madhav102398
ID: 10766860
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.
0
 
LVL 12

Expert Comment

by:dfiala13
ID: 10767035
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.
0
 

Author Comment

by:madhav102398
ID: 10767107
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.
0
 
LVL 12

Accepted Solution

by:
dfiala13 earned 500 total points
ID: 10767239
ADO does do connection pooling, as of version 2.0 I believe.  Here's some info more likely to be of use to you...

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnmdac/html/pooling2.asp

Looks like you might have to do some registry work to manage its behavior.
0
 

Author Comment

by:madhav102398
ID: 10767802
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_ATTR_CONNECTION_POOLING = 201
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_POOLING, _
                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.
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

Author Comment

by:madhav102398
ID: 10768002
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.
0
 

Author Comment

by:madhav102398
ID: 10768390
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.
0
 
LVL 12

Expert Comment

by:dfiala13
ID: 10768411
Glad you figured out how to turn off the pool.
0
 
LVL 12

Expert Comment

by:dfiala13
ID: 10770887
Hmm.... Guess no one even pointed him in the right direction.  Go ahead and close it.
0
 
LVL 12

Expert Comment

by:monosodiumg
ID: 10776506
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.

0
 
LVL 12

Expert Comment

by:dfiala13
ID: 10776583
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. ;)
0
 

Author Comment

by:madhav102398
ID: 10776677
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.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now