DOCUMENT:Q308980 28-DEC-2001 [vbwin] TITLE :PRB:Native Error Not Sent Back to ADO When You Run SQL XML Query PRODUCT :Microsoft Visual Basic for Windows PROD/VER::2.6,6.0 OPER/SYS: KEYWORDS:kbATM kbGrpDSVBDB kbDSupport ====================================================================== ------------------------------------------------------------------------------- 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 = "" sQuery = sQuery & "SELECT * FROM WRONGTBL FOR XML AUTO" sQuery = sQuery & "" 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 2001.