Microsoft KB Archive/295538

= FIX: Resetting ADO Parameter Object Properties in Loop Causes Memory Leak =

Article ID: 295538

Article Last Modified on 9/26/2005

-

APPLIES TO


 * Microsoft Data Access Components 2.5
 * Microsoft Data Access Components 2.5 Service Pack 1
 * Microsoft Data Access Components 2.5 Service Pack 2
 * Microsoft Data Access Components 2.6
 * Microsoft Data Access Components 2.6 Service Pack 1

-



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 &quot;Steps to Reproduce Behavior&quot; section can be rewritten as follows: Parm.Type = adVarChar Parm.Size = 11 While True Parm.Value = &quot;172-32-1176&quot; ' The following line will work around the memory leak as well. ' Set Cmd.ActiveConnection = Conn Cmd.CommandText = &quot;SP_AUTHOR_BY_ID&quot; 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.



Steps to Reproduce the Behavior
To reproduce this problem, follow these steps:   Paste the following code in a new Microsoft Visual Basic project:

Note You must change the User ID value and the 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 &quot;Provider=SQLOLEDB;Server=MyServer;User Id= ;password= ;Initial Catalog=pubs;&quot; 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 = &quot;172-32-1176&quot; ' 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 = &quot;SP_AUTHOR_BY_ID&quot; Set Rst = Cmd.Execute Rst.Close Wend  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.  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  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.

Keywords: kbbug kbfix kbqfe kbmdac260sp2fix kbhotfixserver KB295538

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.