PRB: Native Error Is Not Sent Back to ADO When You Run a SQL XML Query

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).

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:

For additional information, click the article number below to view the article in the Microsoft Knowledge Base: