ADO 2.5 vs 2.6 with SQL OLE DB provider

ADO 2.5 vs 2.6 with SQL OLE DB provider

Post by Christopher Fosket » Tue, 17 Oct 2000 04:00:00



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

 
 
 

ADO 2.5 vs 2.6 with SQL OLE DB provider

Post by Hakan Ere » Thu, 19 Oct 2000 09:58:05


  if( (m_pConnection->Errors->Count) > 0)  {
    long nCount = m_pConnection->Errors->Count;
    // Collection ranges from 0 to nCount -1.
    for(long i = 0;i < nCount;i++) {
      pErr = m_pConnection->Errors->GetItem(i);
      strErr.Format("Err no: %X", pErr->Number);
      strErr += "\n\nDescription : " + pErr->Description;
      MessageBox(NULL, strErr, "Error Message (Provider)", MB_ICONERROR |
MB_APPLMODAL);
    }
  }

code returns all provider errors.
Using MDAC 2.5 or 2.6 does not matter.
You can also view com errors. catching _com_error &e

Have a look at ADO example codes..

 
 
 

ADO 2.5 vs 2.6 with SQL OLE DB provider

Post by Christopher Fosket » Thu, 19 Oct 2000 04:00:00


From my sample apps, it appears that if there are multiple query/action
statements in the batch (in the stored procedure) then you can't get the
errors unless you use the rs.NextRecordset technique.

But this thread is really related to the use of "adExecuteNoRecords".  See
my other post titled "ADO 2.6 Bug... No error raised from MSDASQL provider
if adExecuteNoRecords".

Once an error is raised then I use VB code similar to the code you provided
to get all the error information.

Thanks, Chris.

--
Christopher Foskett
  Avinon, Inc.

  http://www.avinon.com

 
 
 

1. ADO 2.5 Sql Server Ole Db Provider and Memory Loss

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

2. Database capacity

3. ADO 2.6 and SQL Server OLE DB Provider

4. Best way to open documents stored on the database

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

6. Locked reports ?!

7. Memory leak SQLServer 6.5 related to network changes ?

8. ADO 2.5 vs 2.6 problems

9. ADO 2.5 vs. 2.6

10. Comparing OLE DB Providers for Oracle - Microsoft Vs Oracle Vs Merant

11. strange performance-problems after upgrading from ADO 2.5 to ADO 2.6

12. ADO 2.5 or ADO 2.6