CAUSE
Consider the following ActiveX Data Objects (ADO) code,
which is written in Visual Basic for Applications:
Note You must change UID=<username> and
PWD=<strong password> to the correct values before you run this code. Make
sure that UID has the appropriate permissions to perform this operation on the database.ion on the
database.
Dim cn As New ADODB.Connection
Dim cm As New ADODB.Command
Dim rs1 As New ADODB.Recordset
Dim rs2 As ADODB.Recordset
Dim rs3 As New ADODB.Recordset
cn.Open "DSN=Pubs;UID=<username>;PWD=<strong password>;"
On Error Resume Next
cn.Execute "drop procedure dbo.sp_AdoTest"
cn.Execute "create proc sp_AdoTest ( @InParam int ) as " & _
"select * FROM Authors WHERE State <> 'CA' " & _
"return @InParam + 10"
cn.Execute "drop procedure dbo.sp_AdoTest2"
cn.Execute "create proc sp_AdoTest2 as " & _
"select * FROM Authors WHERE State <> 'CA' " & _
"return 10"
Set cm.ActiveConnection = cn
cm.CommandType = adCmdStoredProc
cm.CommandText = "sp_AdoTest"
cm.Parameters.Refresh
cm.Parameters(1).Value = 10
Set rs1 = cm.Execute
Debug.Print cm.Parameters.Count
At this point, the Parameters collection for the command object has two
objects, and you can reference them as you would normally. However, consider
the following code, which destroys (resets) the Parameters collection:
cm.CommandText = "sp_AdoTest2"
cm.Parameters.Refresh
Set rs1 = cm.Execute
Debug.Print cm.Parameters.Count
At this point, the Parameters collection has been reset to contain only
one Parameter object. However, the original Recordset (rs1), which has not been
closed or released, still has a reference to an internal buffer that stores the
value of each parameter that was originally created. When the original
recordset is closed, it attempts to release the buffer. However, the second
recordset's Parameters collection may be using the same memory location for its
own buffers, and if both recordsets are trying to release the same memory
location, you may receive an Access Violation.
rs2.Close
rs1.Close
cn.Close
The Access Violation has been confirmed to occur (at random intervals)
when using a forward only cursor with a parameterized stored procedure using
the OLE DB Provider for ODBC to the SQL Server ODBC Driver. It may occur with
other ODBC Drivers or OLE DB Providers as well.
The Access Violation
appears to have been fixed in the MDAC 2.1 SP2 release. However, recycling the
same Command Object for multiple, concurrently open Recordsets is not a safe
practice. Any savings in performance or memory allocation you receive in
minimizing the number of created objects will not be compensated for by
increased risk of your code causing a failure within ADO. The Command Object is
not designed or intended for this kind of utilization.