Q308980: PRB:Native Error Not Sent Back to ADO When You Run SQL XML Query
Article: Q308980
Product(s): Microsoft Visual Basic for Windows
Version(s): 2.6,6.0
Operating System(s):
Keyword(s): kbATM kbGrpDSVBDB kbDSupport
Last Modified: 28-DEC-2001
-------------------------------------------------------------------------------
The information in this article applies to:
- Microsoft Visual Basic Professional Edition for Windows, version 6.0
- Microsoft Visual Basic Enterprise Edition for Windows, version 6.0
- Microsoft Data Access Components version 2.6
- Microsoft SQL Server 2000 (all editions)
- Microsoft XML, version 2.6
-------------------------------------------------------------------------------
SYMPTOMS
========
If an error occurs when you use a SQL XML query to retrieve Extensible Markup
Language (XML) data, the native error that SQL Server generates is not sent back
to the errors collection of ActiveX Data Objects (ADO).
MORE INFORMATION
================
If a SQL Server error occurs when you open an ADO Recordset object, the SQL
state and native errors are exposed in the errors collection. However, when you
use an ADODB command stream, the native error displays a generic ADO error. No
value is placed for the SQL state.
Steps to Reproduce Behavior
---------------------------
1. Create a new Visual Basic Standard EXE project. Form1 is created by default.
2. On the Project menu, click References, and then select the Microsoft ActiveX
Data Objects 2.6 check box.
3. Place two CommandButton controls on Form1, and then add the following code to
the declarations section of Form1:
Option Explicit
Dim sConn As String
Private Sub Form_Load()
sConn = "Provider=SQLOLEDB;Data Source=YourServer;Initial " & _
"Catalog=Northwind;User ID=YourUser;Password=YourPassword;"
End Sub
Private Sub Command1_Click()
Dim sQuery As String
Dim adoConn As ADODB.Connection
Set adoConn = New ADODB.Connection
adoConn.ConnectionString = sConn
adoConn.CursorLocation = adUseClient
adoConn.Open
Dim adoRs As ADODB.Recordset
Set adoRs = New ADODB.Recordset
On Error Resume Next
adoRs.Open "SELECT * FROM WRONGTBL FOR XML AUTO", adoConn
Dim aer As ADODB.Error
For Each aer In adoConn.Errors
Debug.Print "Recordset Object"
Debug.Print "Error Description: " & aer.Description
Debug.Print "Native Error: " & aer.NativeError
Debug.Print "SQL State: " & aer.SQLState
Next
On Error GoTo 0
End Sub
Private Sub Command2_Click()
Dim sQuery As String
Dim outStrm
Dim adoConn As ADODB.Connection
Set adoConn = New ADODB.Connection
adoConn.ConnectionString = sConn
adoConn.CursorLocation = adUseClient
adoConn.Open
Dim adoCmd As ADODB.Command
Set adoCmd = New ADODB.Command
Set adoCmd.ActiveConnection = adoConn
sQuery = "<ROOT xmlns:sql='urn:schemas-microsoft-com:xml-sql'>"
sQuery = sQuery & "<sql:query>SELECT * FROM WRONGTBL FOR XML AUTO"
sQuery = sQuery & "</sql:query></ROOT>"
Dim adoStreamQuery As ADODB.Stream
Set adoStreamQuery = New ADODB.Stream
' Open the command stream so it can be written to.
adoStreamQuery.Open
' Set the input command stream's text with the query string.
adoStreamQuery.WriteText sQuery, adWriteChar
' Reset the position in the stream; otherwise, it is at EOS.
adoStreamQuery.Position = 0
' Set the command object's command to the input stream that you set above.
Set adoCmd.CommandStream = adoStreamQuery
' Set the dialect for the command stream to a SQL query.
adoCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"
' Create the output stream to stream the results into.
Set outStrm = CreateObject("ADODB.Stream")
outStrm.Open
' Set command's output stream to the output stream that you just opened.
adoCmd.Properties("Output Stream") = outStrm
' Run the command, which therefore fills the output stream.
On Error Resume Next
adoCmd.Execute , , adExecuteStream
Dim aer As ADODB.Error
For Each aer In adoConn.Errors
Debug.Print "Command Stream Object"
Debug.Print "Error Description: " & aer.Description
Debug.Print "Native Error: " & aer.NativeError
Debug.Print "SQL State: " & aer.SQLState
Next
On Error GoTo 0
End Sub
4. Modify the connection string in sConn to reflect the server name and
credentials that are used to connect to your SQL Server 2000 server.
5. Note that the SQL statement deliberately attempts to query a nonexistent
table (WRONGTBL).
6. Run the project, and then click Command1. You receive output similar to the
following:
Error Description: Invalid object name 'WRONGTBL'.
Native Error: 208
SQL State: 42S02
7. Click Command2. Note that the error description is the same, but the native
error is a generic ADO error number, and the SQL state is an empty string:
Command Stream Object
Error Description: Invalid object name 'WRONGTBL'.
Native Error: -2147217865
SQL State:
REFERENCES
==========
For additional information, click the article number below to view the article
in the Microsoft Knowledge Base:
Q271620 Retrieve XML Data by Using a SQL XML Query in a Visual Basic Client
Additional query words: SQLState
======================================================================
Keywords : kbATM kbGrpDSVBDB kbDSupport
Technology : kbVBSearch kbSQLServSearch kbAudDeveloper kbZNotKeyword6 kbMSXMLSearch kbSQLServ2000Search kbZNotKeyword2 kbVB600Search kbVB600 kbMDACSearch kbMDAC260 kbSQLServ2000 kbMSXML260
Version : :2.6,6.0
Issue type : kbprb
=============================================================================
THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.
Copyright Microsoft Corporation 1986-2002.