SQL Server 6.5 - Connections not being killed automatically

[kentran]

Im using ADO in ASP to make connections to a SQL Server 6.5 Server.
The problem is that the connection to the DB is remaining in the system activity window and is not being removed until 50-60 seconds.
This causes the maximim connections (15) to be reached and sql server errors occuring as result.
I expected the connections to be removed immediately.

I explicity create my ADO objects and close and set to nothing as appropriate

This is the type of code (not exact):

dim aCmd
dim aCon
dim aRs

set aCon = strCon

aCmd.ActiveConnection = aCon
aCmd.CommandType = adCmdText
aCmd.CommandText = "select ..."

set aRs = aCmd.Open

set aCmd.ActiveConnection = nothing
set aRs = nothing

 

[tlbroadbent]

Try changing the order of the set statements. Attempting to destroy the connection befoe the record set may fail.

set aRs = nothing
set aCmd.ActiveConnection = nothing

Although, it may not be needed, I would include explicit close statements for the record set and connection. Add these statements prior to the Set statements.

aRs.close
aCmd.ActiveConnection.close

You could be encountering a bug. See the following for possibilities.
[ignore]

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q252482

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q288409

Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.