OLEDB Rich Error info via ADO

[Jonni Faiga]

Hi,
I think the following is a bug in the OLEDB driver for SQL Server...

I can get ADO errors no problem, however when using SQLOLEDB, I get an empty
Err.Description from the
ADO.Error object. Below is a code snippet from MSDN

===============
'BeginDescriptionVB
Public Sub DescriptionX()

Dim Cnxn As ADODB.Connection
Dim Err As ADODB.Error
Dim strError As String

On Error GoTo ErrorHandler

' Intentionally trigger an error
Set Cnxn = New ADODB.Connection
Cnxn.Open "nothing"

Exit Sub

ErrorHandler:

' Enumerate Errors collection and display
' properties of each Error object
For Each Err In Cnxn.Errors

strError = "Error #" & Err.Number & vbCr & _
" " & Err.Description & vbCr & _
" (Source: " & Err.Source & ")" & vbCr & _
" (SQL State: " & Err.SQLState & ")" & vbCr & _
" (NativeError: " & Err.NativeError & ")" & vbCr
If Err.HelpFile = "" Then
strError = strError & " No Help file available"
Else
strError = strError & _
" (HelpFile: " & Err.HelpFile & ")" & vbCr & _
" (HelpContext: " & Err.HelpContext & ")" & _
vbCr & vbCr
End If

Debug.Print strError
Next

Resume Next

End Sub
'EndDescriptionVB
==================

When running this, I get the following in the Immediate Window:
Error #-2147467259
[Microsoft][ODBC Driver Manager] Data source name not found and no default
driver specified
(Source: Microsoft OLE DB Provider for ODBC Drivers)
(SQL State: IM002)
(NativeError: 0)
No Help file available


However, thats were it stops. If I open a connection via SQLOLEDB and call
a store procedure that doesnt exist, all I get is a -2147217900 error. The
Err.Description is empty.

*** HOWEVER if I run the same code using MSDASQL.1 (OLE DB provider for SQL),
then I get the rich error information!!! ***

Error #-2147217900
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not find stored procedure
'sp_NonExistant'.
(Source: Microsoft OLE DB Provider for ODBC Drivers)
(SQL State: 37000)
(NativeError: 2812)
No Help file available


Do you know anything about this bug? Do you know if Microsoft is planning
to release a SQL 2000 SP1, or a hotfix?
ps. I have found another bug with SQL 2000, is that if you install just the
Client Side tools, they have to go on C: !

[Jonni Faiga]

Using SQL Server 2000, and Windows 2000 SP1:

If I use the OLE DB provider for MS SQL Server (SQLOLEDB) Provider, then
I just get an empty error description for something like calling a store
procedure that doesnt exist. However I can get it if use the OLE DB provider
for ODBC (MSDASQL):
SQLState = 37000
NativeError = 2812
Description = [Microsoft][ODBC SQL Server Driver][SQL Server]Could not find
stored procedure 'sp_Timeout_TimeoutExpiredTEMP'.

But, if I use ODBC, then it is not XML aware, and so if I try:
cmd.Properties("Output Stream") = oStreamXML

Then I get the ADO error:
"Item cannot be found in the collection corresponding to the requested name
or ordinal." (Err.Number: 3265) (actually: -2146825023)

I would love to know what can I do to have rich error information and use
Streams????

[Jonni Faiga]

The following article is related, but doesnt provide a workaround to the problem
described below.
http://support.microsoft.com/support.../Q235/3/40.ASP

"Jonni Faiga" <jonni@bigfoot.com> wrote:
>
>Using SQL Server 2000, and Windows 2000 SP1:
>
>If I use the OLE DB provider for MS SQL Server (SQLOLEDB) Provider, then
>I just get an empty error description for something like calling a store
>procedure that doesnt exist. However I can get it if use the OLE DB provider
>for ODBC (MSDASQL):
>SQLState = 37000
>NativeError = 2812
>Description = [Microsoft][ODBC SQL Server Driver][SQL Server]Could not find
>stored procedure 'sp_Timeout_TimeoutExpiredTEMP'.
>
>But, if I use ODBC, then it is not XML aware, and so if I try:
>cmd.Properties("Output Stream") = oStreamXML
>
>Then I get the ADO error:
>"Item cannot be found in the collection corresponding to the requested name
>or ordinal." (Err.Number: 3265) (actually: -2146825023)
>
>I would love to know what can I do to have rich error information and use
>Streams????