FIX: Connection Leak Using Parameterized Command In ADO

• To resolve this problem, obtain the latest service pack for Windows 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
  260910 How to Obtain the Latest Windows 2000 Service Pack
• To resolve this problem, obtain the latest service pack for Microsoft Data Access Components 2.5. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
  293312 INFO: How to Obtain the Latest MDAC 2.5 Service Pack

File name       Date        Size      Version      
-----------------------------------------------------
Msado15.dll     1/26/2000   329KB     2.12.4926.0
				

1. Microsoft Data Access Objects 2.1 SP2 is installed.
2. A parameterized ADO Command object is used.
3. The Java COM object is hosted in MTS or COM+.
4. The Java COM object creates a disconnected recordset.

Steps to Reproduce the Behavior

1. Create a Java COM object project named ConnLoss with Visual J++ 6.0 using the following code:

   import com.ms.wfc.data.*;
   
   public class ConnLoss
   {
     // Modify this connection string to point to a running SQL Server.
     private static String m_connect = 
       "Provider=SQLOLEDB;Server=(Local);Database=Pubs;UID=sa;PWD=;";
     public com.ms.wfc.data.adodb._Recordset 
       FindAuthorsLastName( String au_id, boolean fCallGC )
     {
       Connection conn = null;
       Command cmd   = null;
       Recordset rs  = null;
       try
       {     
         // Open connection to SQL Server.
         conn = new Connection();
         conn.setCursorLocation( AdoEnums.CursorLocation.CLIENT );
         conn.open( m_connect );
         
         // Prepare command object.
         cmd = new Command();
         cmd.setActiveConnection( conn );
         
         cmd.setCommandText( "select au_lname from authors where au_id=?" );
       
         cmd.getParameters().append( 
           cmd.createParameter( "au_id", 
                      AdoEnums.DataType.VARCHAR,
                      AdoEnums.ParameterDirection.INPUT,
                      20, au_id ) );
         
         // Execute command.
         rs = cmd.execute();
         
         // Disconnect recordset and close connection.
         rs.setActiveConnection( (Connection) null );
         conn.close();
         
         // Call gc if requested.
         if (fCallGC) System.gc();
         
         // Return disconnected recordset.
         return (com.ms.wfc.data.adodb._Recordset) rs.getDataSource();
         
       }
       catch( AdoException adoEX )
       {
         // Log errors here.
       }
       return null;
     }
   }
   					

2. Add the Java COM object to MTS or the COM+ package.
3. Call the Java COM object with the following Microsoft Visual Basic for Applications (VBA) code:

   Sub TestConnLoss()
   Dim objCL As Object
   Dim i As Long
   Dim rs As ADODB.recordset
     set objCL = CreateObject("ConnLoss.ConnLoss")
     For i = 1 To 100
       Set rs = objCL.FindAuthorsLastName("756-30-7391", False)
       Debug.Print rs.Fields("au_lname").Value
       rs.Close
       Set rs = Nothing    
     Next i    
   End Sub
   					

4. Run Windows NT Performance Monitor on the machine where SQL Server 7.0 is located and monitor User Connections under the SQL Server:General Statistics performance counter.
5. Run the VBA client code. At this point, 100 user connections are generated by the code as reported by Windows NT Performance Monitor, indicating that the OLE DB session pooling is not working properly for the SQL OLE DB provider used by the Java COM object.
   
   
6. Change the second parameter of FindAuthorsLastName to True in order to activate the System.gc() code.
7. Stop and restart the MTS or COM+ package.
8. Run the VBA client code a second time.