ADO 2.5 Sql Server Ole Db Provider and Memory Loss

ADO 2.5 Sql Server Ole Db Provider and Memory Loss

Post by Robert » Mon, 10 Jun 2002 05:56:08



I have noticed a memory Loss in this case:

Suppose you have a parser that takes the following file:

INSERT INTO PIPPO (A,B,C,D) VALUES (1,2,3,4);
UPDATE PIPPO.....
INSERT INTO PIPPO (A,B,C,D) VALUES (4,5,6,7);
....
....
Then you have the following istructions in your program......

Public Function ExecuteSQL( _
    sql As String, _
    Optional outError As Variant _
  ) As Boolean
Dim localComm As New ADODB.Command

    Set localComm.ActiveConnection = mDB
    localComm.Prepared = False
    localComm.CommandText = sql
    localComm.Execute
    ExecuteSQL = True
    Set localComm = Nothing

End Function

I have noticed that the MEMORY increases for the process DURING EXECUTION
(SLOWLY BUT INCREASE). It seems that there is a CACHING of THE ISTRUCTIONS
....

But if THE SQL ISTRUCTIONS TO BE PARSERIZED ARE

SELECT * FROM PIPPO WHERE 1= 1
SELECT * FROM PIPPO WHERE 1= 1 (IDENTICAL FIRST SQL INSTRUCTION)
SELECT * FROM PIPPO WHERE 1= 1 (IDENTICAL FIRST SQL INSTRUCTION)
SELECT * FROM PIPPO WHERE 1= 1 (IDENTICAL FIRST SQL INSTRUCTION)

The memory doesn't INCREASE when program is executed
How can I can SAY to ADO TO DON'T USE CACHING FOR INSTRUCTIONS ?

Thanks

 
 
 

1. ADO 2.5 vs 2.6 with SQL OLE DB provider

I had not seen this anywhere in the ADO books but here is a strange piece of
behavior and it appears the ADO team fixed a bug in 2.6...

It is fairly well known that if you use the SQL OLE DB provider and you
experience errors in your stored procedure, that you won't get error
information unless you either use SET NOCOUNT ON in your stored proc, or use
the rs.NextRecordset functionality to loop through all the results to see if
an error occurred.

However, in ADO 2.5 if you use the adExecuteNoRecords and did not use SET
NOCOUNT ON, then you will never get the errors!  Ouch.  Obviously this would
have a dramatic effect on your transactions if you could not tell that an
error occurred in your stored proc.

But it appears that 2.6 has fixed this.  If you use SET NOCOUNT ON then you
get the error regardless of whether or not you used adExecuteNoRecords.  If
you are using the recordset.NextRecordset technique then you will still get
the errors even if you specified adExecuteNoRecords.

Which leads me to ask... is adExecuteNoRecords basically disabled now?  The
original intent was that you used adExecuteNoRecords to avoid the overhead
of getting a recordset object from the Execute method even though you were
just doing action statements like insert/update/delete.  So, if we're now
able to use the Recordset.NextRecordset technique to inspect for errors,
then I guess we're getting a recordset back.  Maybe the ADO team could shed
some light on this???

However, it appears that we still only get the first error that occurred.
If you want ALL the errors that occurred then you need to use the ODBC
provider.

Please let me know if you have experienced different behavior.

Thanks, Chris.

--
Christopher Foskett
  Avinon, Inc.

  http://www.avinon.com

2. CO; DTC Denver; Contract; Sybase DBA; C, C++

3. Microsoft OLE DB Provider for SQL Server 80004005: Unspecified Error followed by Insufficient memory

4. A problem with interbase and delphi... .locate function

5. ADO 2.6 and SQL Server OLE DB Provider

6. triggers

7. Error in Initializing Provider with OLE DB Provider for SQL Server

8. Problem with connection PC to INFORMIX ONLINE 7.22

9. [ODBC SQL Server Driver][SQL Server][OLE/DB provider returned message: Timeout expired]

10. New features in ADO 2.5 / Jet OLE DB 4.0

11. SQL Server linked server with OLE DB provider for Visual Foxpro

12. link server on sql server using ole db provider for odbc driver

13. ODBC provider vs MS SQL provider (OLE DB)