FIX: Resetting ADO Parameter Object Properties in Loop Causes Memory Leak (295538)


The information in this article applies to:

  • Microsoft Data Access Components 2.5
  • Microsoft Data Access Components 2.5 SP1
  • Microsoft Data Access Components 2.5 SP2
  • Microsoft Data Access Components 2.6
  • Microsoft Data Access Components 2.6 SP1
This article was previously published under Q295538

SYMPTOMS

When you set the Size or Type property for an ADODB.Parameter object repeatedly, a memory leak may occur.

WORKAROUND

To work around the problem, only change the Value property of the Parameter object in the loop, not other properties such as Size and Type. For example, the code shown in the "Steps to Reproduce Behavior" section can be rewritten as follows: 
   Parm.Type = adVarChar
   Parm.Size = 11
   While True
        Parm.Value = "172-32-1176"<BR/>
        ' The following line will work around the memory leak as well. 
        ' Set Cmd.ActiveConnection = Conn
        Cmd.CommandText = "SP_AUTHOR_BY_ID"
        Set Rst = Cmd.Execute
        Rst.Close
    Wend
Resetting the ActiveConnection property of the Command object before resetting the parameter information will work around the memory leak.

RESOLUTION

To resolve this problem, obtain the latest service pack for Microsoft Data Access Components 2.6. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

300635 INFO: How to Obtain the Latest MDAC 2.6 Service Pack

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article. This problem was first corrected in Microsoft Data Access Components 2.6 Service Pack 2.

MORE INFORMATION

Steps to Reproduce the Behavior

To reproduce this problem, follow these steps:
  1. Paste the following code in a new Microsoft Visual Basic project:

    Note You must change the User ID <username> value and the password =<strong password> value to the correct values before you run this code. Make sure that User ID has the appropriate permissions to perform this operation on the database.
        Dim Conn As ADODB.Connection
    Dim Cmd As ADODB.Command
    Dim Parm As ADODB.Parameter
    Dim Rst As ADODB.Recordset
    Set Conn = New ADODB.Connection
    
    Conn.Open "Provider=SQLOLEDB;Server=MyServer;User Id=<user name>;password=<strong password>;Initial Catalog=pubs;"
    
    Set Cmd = New ADODB.Command
    Cmd.ActiveConnection = Conn
    Cmd.CommandType = adCmdStoredProc
    
    Set Parm = New ADODB.Parameter
    Parm.Type = adVarChar
    Parm.Size = 10
    Parm.Direction = adParamInput
    
    Cmd.Parameters.Append Parm
    
    While True
        Parm.Type = adVarChar
        Parm.Size = 11
        Parm.Value = "172-32-1176"
        ' The following line will work around the memory leak, 
        ' but has been commented out here to demonstrate the problem.
        ' Set Cmd.ActiveConnection = Conn
        Cmd.CommandText = "SP_AUTHOR_BY_ID"
        Set Rst = Cmd.Execute
        Rst.Close
    Wend
  2. Add a reference to the Microsoft ActiveX Data Objects library on a computer with a version of Microsoft Data Access Components (MDAC) earlier than version 2.6 SP2.
  3. Create the following stored procedure in the pubs sample database on the same SQL Server server used in the connection string above:
    CREATE Procedure SP_AUTHOR_BY_ID @AuthorID VarChar(11)
As
SELECT * FROM Authors WHERE AuID = @AuthorID
  4. Use the Windows Performance Monitor tool to monitor private bytes on the VB6 process for this sample (or the compiled executable name, if you run the sample outside of the IDE after compiling it). Run the sample, and observe that the number of private bytes allocated to the process continues to grow slowly without shrinking.
Modification Type:MinorLast Reviewed:9/26/2005
Keywords:kbHotfixServer kbQFE kbbug kbfix kbmdac260sp2fix KB295538 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.