ADO 2.6 bug... no error raised from MSDASQL provider if adExecuteNoRecords

ADO 2.6 bug... no error raised from MSDASQL provider if adExecuteNoRecords

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



Not sure where else to post bugs... please let me know if you know...

If you use the MSDASQL (OLE DB Provider for ODBC Drivers) with the
adExecuteNoRecords option set, then you will get no errors raised if an
error occurs in your stored procedure.

Also, if you use SET NOCOUNT ON in your stored procedure (regardless of
adExecuteNoRecords) then you will get no errors raised if an error occurs in
your stored procedure.

This is so frustrating because it is almost the opposite behavior from the
SQLOLEDB provider.  It is very hard to code generic Data Access Layer
components if the behavior is all over the map.  Hopefully the ADO team can
do some work to build some consistency across the providers as well.

Example:

        moCMD.CommandType = adCmdStoredProc
        moCMD.CommandText = sSQL
        moCMD.Execute RecordsAffected, , adExecuteNoRecords

If using MSDASQL but errors occur in your stored procedure then NO error
will be raised.  Ouch.

As a result of all of this we are not allowing users of our DAL component to
use DSNs.  I have attached the test stored procedure.

Chris.

--
Christopher Foskett
  Avinon, Inc.

  http://www.avinon.com

begin 666 TestOLEDBErrorsSp.txt
<encoded_portion_removed>

 
 
 

ADO 2.6 bug... no error raised from MSDASQL provider if adExecuteNoRecords

Post by George Poulos » Wed, 18 Oct 2000 04:00:00


Quote:>If using MSDASQL but errors occur in your stored procedure then NO error
>will be raised.  Ouch.

This is not true. Check the <State> property of the rs returned from
<Execute>.

You may want to test this using Query Tool (using ADO) from:
http://gpoulose.home.att.net/

George


> Not sure where else to post bugs... please let me know if you know...

> If you use the MSDASQL (OLE DB Provider for ODBC Drivers) with the
> adExecuteNoRecords option set, then you will get no errors raised if an
> error occurs in your stored procedure.

> Also, if you use SET NOCOUNT ON in your stored procedure (regardless of
> adExecuteNoRecords) then you will get no errors raised if an error occurs
in
> your stored procedure.

> This is so frustrating because it is almost the opposite behavior from the
> SQLOLEDB provider.  It is very hard to code generic Data Access Layer
> components if the behavior is all over the map.  Hopefully the ADO team
can
> do some work to build some consistency across the providers as well.

> Example:

>         moCMD.CommandType = adCmdStoredProc
>         moCMD.CommandText = sSQL
>         moCMD.Execute RecordsAffected, , adExecuteNoRecords

> If using MSDASQL but errors occur in your stored procedure then NO error
> will be raised.  Ouch.

> As a result of all of this we are not allowing users of our DAL component
to
> use DSNs.  I have attached the test stored procedure.

> Chris.

> --
> Christopher Foskett
>   Avinon, Inc.

>   http://www.avinon.com


 
 
 

ADO 2.6 bug... no error raised from MSDASQL provider if adExecuteNoRecords

Post by Christopher Fosket » Wed, 18 Oct 2000 04:00:00


Actually, it is true...

My explanation and example code said that the problem is related to
"adExecuteNoRecords".  So, there is NO recordset on which to check the
<state> property.

Posting Example again...

Example:

        moCMD.CommandType = adCmdStoredProc
        moCMD.CommandText = sSQL
        moCMD.Execute RecordsAffected, , adExecuteNoRecords

If you use adExecuteNoRecords, then you don't have a recordset, so you
cannot check the <State> property.  Even if you did do something like "set
oRS = moCmd.Execute(,RecordsAffected,,adExecuteNoRecords)" then oRS would
still be = Nothing.

Thanks, Chris.

--
Christopher Foskett
  Avinon, Inc.

  http://www.avinon.com

 
 
 

ADO 2.6 bug... no error raised from MSDASQL provider if adExecuteNoRecords

Post by George Poulos » Wed, 18 Oct 2000 04:00:00


I agree.

George


> Actually, it is true...

> My explanation and example code said that the problem is related to
> "adExecuteNoRecords".  So, there is NO recordset on which to check the
> <state> property.

> Posting Example again...

> Example:

>         moCMD.CommandType = adCmdStoredProc
>         moCMD.CommandText = sSQL
>         moCMD.Execute RecordsAffected, , adExecuteNoRecords

> If you use adExecuteNoRecords, then you don't have a recordset, so you
> cannot check the <State> property.  Even if you did do something like "set
> oRS = moCmd.Execute(,RecordsAffected,,adExecuteNoRecords)" then oRS would
> still be = Nothing.

> Thanks, Chris.

> --
> Christopher Foskett
>   Avinon, Inc.

>   http://www.avinon.com

 
 
 

1. Errors raising errors using ADO 2.6

A co-worker recently installed the SQL Server 2000 client tools on his
Windows 2000 Pro machine.  This also stuck ADO 2.6 RTM on his machine.
Whenever an error occurs using ADO, it is not raised up correctly e.g.
instead of something like "Login failed for user bill" he gets "Method
'Open' of object '_Connection failed".  Everything seems to function
correctly as long as no errors are generated i.e. he can still run queries
and open recordsets etc

Has anyone seen this problem before?  Or even better know of a really simple
fix?

Thanks
James Mahoney

2. Ambiguous NUMERIC type

3. ADO Parameters and MSDASQL Provider

4. HELP needed parsing a .CSV file into a table - Best Techniques?

5. ADO Provider Help! (MSDASQL.1 vs SQLOLEDB.1)

6. HP NNM data collection on SQL SNMP agent

7. ADO Parameters and MSDASQL Provider

8. Must be at least one record?

9. Error 7399: OLE DB provider 'MSDASQL'

10. Error 3704 using MSDASQL provider

11. ADO 2.6 and SQL Server OLE DB Provider

12. ADO 2.5 vs 2.6 with SQL OLE DB provider

13. OLAP Provider Missing but do not wish to install MDAC 2.6 to install OLAP provider