Take the 2-minute tour ×

I have a customer who's Classic ASP application is generating ASP_0147 errors. The first thing I'm checking is that they're closing and releasing SQL/ADO resources timeously.

Their code has the following pattern:

Function GetXXXXRecordSet()
  Set objConn = Server.CreateObject("ADODB.Connection")
  With objConn 
    .CursorLocation = 3 ''adUseServer (default)
    .ConnectionString = strConnectionString
  End With

  Set objCmd = Server.CreateObject("ADODB.Command")
  Set objCmd.ActiveConnection = objConn

  '' Build command object to call SQL stored proc, snipped for brevity

  Set objRs = Server.CreateObject("ADODB.RecordSet")
  objRs.Open objCmd, ,3,4 '' Cursor=adOpenStatic, Locktype=adLockBatchOptimistic

  '' Return Recordset
  Set GetXXXXRecordSet = objRs

  If Not objCmd Is Nothing Then
    objCmd.ActiveConnection = Nothing  '' Should this use a Set statement?
    Set objCmd = Nothing
  End If
  If Not ObjRs Is Nothing The Set objRs = Nothing
End Function

Does setting an ADO Command's ActiveConnection = Nothing close the underlying SQL Connection or does that have to be closed explicitly?

Also should the line:

objCmd.ActiveConnection = Nothing


Set objCmd.ActiveConnection = Nothing

Oddly the first version doesn't generate an error which is why I ask.

It's been so long since I looked at ADO and my knowledge is somewhat rusty.

share|improve this question
Should be: Set objCmd.ActiveConnection = Nothing indeed –  Younes Mar 3 '10 at 14:08
@Kev: I'm curious did adding a connection .Close to the code above fix the problem? –  AnthonyWJones Mar 5 '10 at 22:18
@AnthonyWJones - I've passed the recommendations back to the customer, now just got to wait and see. –  Kev Mar 6 '10 at 3:21
add comment

5 Answers

up vote 3 down vote accepted

My understanding was always that setting ActiveConnection to Nothing did not close the connection it just removed it from that object, this is useful for things like Recordsets where you want a fixed read-only snapshot of the Recordset (combined with settings the correct cursor options) and so don't need to keep the connection live for that Recordset (but may need the connection still open for other operations)

AFAIK only actually calling objConn.Close closes the connection and Set objConn = Nothing frees up the memory

share|improve this answer
That's what I thought, just wanted to confirm. –  Kev Mar 5 '10 at 16:48
Re-read the MSDN docs and the phraseology they use is: "...disassociates the Command object from the current Connection". –  Kev Mar 5 '10 at 17:00
add comment

Closing a ADODB RS + CONN:

Set objRS = Nothing
Set objConn = Nothing

So should be: Set objCmd.ActiveConnection = Nothing indeed




share|improve this answer
add comment

Been awhile for all this but wouldn't setting it to nothing just clear the object. I bet if you monitored SQL Server the connection isn't terminated then.

share|improve this answer
That's what I thought too, just wanted to confirm. –  Kev Mar 5 '10 at 16:50
add comment

VBScript is garbage-collected, and even provides very clear and explicit guarantees regarding the GC's timing. Setting a local variable to nothing right before it goes out of scope is completely redundant because the end of the function will do the same thing, and the GC will clean up the object.

The only question is whether the ADODB.Connection's destructor frees the database resources. I'm 99% certain that it does. If so, just letting the Connection object go out of scope will free up all associated resources.

share|improve this answer
add comment

Yes, you are correct that the setting of an object to nothing releases memory by using 'Set'...

Set objCmd.ActiveConnection = Nothing

Hope this helps.

share|improve this answer
But does the underlying SQL connection get cleaned up too? –  Kev Mar 3 '10 at 14:26
@Kev: Yes it does get cleaned up too...since you're freeing up the memory occupied by the 'objCmd.ActiveConnection', it would also be prudent to 'Set objCmd = Nothing' also! –  t0mm13b Mar 3 '10 at 15:28
No it doesn't, setting ActiveConnection to nothing only clears the objects reference to the Connection object, it doesn't clear the actual object –  RobV Mar 4 '10 at 16:07
add comment

Your Answer


