ADO Errors collection and SQL Server 7 
Author Message
 ADO Errors collection and SQL Server 7


    I'm trying to get error codes returned from a SQL Server 7 stored
procedure.  According to the MSDN ADO help stuff I've read the SQL Server
error level has to be over 11 for an error to show up.  I've done this, but
nothing ever shows up in the Errors collection of the ADO connection object.

Here's the code I've been trying this on:

Stored Procedure in SQL Server 7:

CREATE PROCEDURE [spr_ErrorReturnTest]


  RAISERROR  ('Test Error', 17, 1)

VB Code:

Private Sub Form_Load()
  Const strCON As String = "Provider=SQLOLEDB.1;Persist Security
Info=False;User ID=*****;Initial Catalog=StrategicAccount;Data
Source=******;User Id=*****;PASSWORD=*****;"

  Dim conDB As ADODB.Connection
  Dim cmdExec As ADODB.Command
  Dim rsData As ADODB.Recordset
  Dim errObj As ADODB.Error

  Set conDB = New ADODB.Connection
  Set cmdExec = New ADODB.Command
  Set rsData = New ADODB.Recordset

  conDB.Open strCON
  Set cmdExec.ActiveConnection = conDB

  cmdExec.CommandType = adCmdStoredProc
  cmdExec.CommandText = "spr_ErrorReturnTest"
  cmdExec.Parameters.Append cmdExec.CreateParameter("IntegerOne", adInteger,
adParamInput, 4, 10)
  cmdExec.Parameters.Append cmdExec.CreateParameter("IntegerTwo", adInteger,
adParamInput, 4, 0)

  rsData.CursorLocation = adUseClient
  rsData.CursorType = adOpenStatic
  rsData.Open cmdExec

  For Each errObj In conDB.Errors
    Debug.Print errObj.NativeError & ": " & Err.Description
  Next errObj

  Set rsData = Nothing
  Set cmdExec = Nothing
  Set conDB = Nothing
End Sub

The errors collection should have a divide by zero error returned from the
Stored procedure along with the 'Test Error'  But when the code executes
there are no error objects in the errors collection!

This is what I get from SQL Server Query Analyzer:
EXEC spr_ErrorReturnTest 10, 0

Server: Msg 8134, Level 16, State 1, Line 0
Divide by zero error encountered.
OpportunityID NoteID      CreatedBy   CreatedOn
 -------------------- --------------------------- --------------
5             4           1           2001-01-15
5             5           1           2001-01-15
(2 row(s) affected)

Server: Msg 50000, Level 17, State 1, Procedure spr_ErrorReturnTest, Line 10
Test Error

From my understanding error message 8134 and 50000 should be in error
objects in the conDB.Errors collection.
If I'm doing something wrong here please let me know.


Sun, 06 Jul 2003 04:05:31 GMT  
 ADO Errors collection and SQL Server 7
I've been messing around with this some more and it seems that if the
provider type is SQLOLEDB then no errors are returned.
If I use a named DSN with provider type MSDASQL then the first error that
occurs causes ADO to raise a run-time error of -2147467259 then the
connection objects Errors collection does contain the first error raised in
the stored procedure.

I don't know what is causing this problem, but at least I can get around it

Of course this beings up another question are there any perfomance
differences between using SQLOLEDB versus MSDASQL?

Sun, 06 Jul 2003 23:28:37 GMT  
 [ 2 post ] 

 Relevant Pages 

1. ADO 2.6 Error Collection and SQL Server 2000

2. errors collection (VB6, SQL Server 7, OLEDBSQL)

3. ADO does not always return Errors collection from SQL 2000

4. MS SQL raiseerror not returned to ADO error collection

5. MS SQL raiseerror not returned to ADO error collection

6. VB Sql Server Via ADO Problem: [Microsoft][ODBC Sql Server Driver]

7. ADO error trapping in VB + SQL Server 2000

8. ADO messages and SQL SERVER 7 errors

9. Data objects closed error in ADO 2.1 and SQL server 6.5

10. sql server 6.5 ado error

11. SQL Server ADO Transaction Error

12. VB/ADO/SQL Server error


Powered by phpBB® Forum Software