Microsoft KB Archive/308980

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

Article ID: 308980

Article Last Modified on 5/8/2003

-

APPLIES TO


 * Microsoft Visual Basic 6.0 Professional Edition
 * Microsoft Visual Basic 6.0 Enterprise Edition
 * Microsoft Data Access Components 2.6
 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft XML Parser 2.6

-



This article was previously published under Q308980



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
 Create a new Visual Basic Standard EXE project. Form1 is created by default. On the Project menu, click References, and then select the Microsoft ActiveX Data Objects 2.6 check box.  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 = &quot;Provider=SQLOLEDB;Data Source=YourServer;Initial &quot; & _ &quot;Catalog=Northwind;User ID=YourUser;Password=YourPassword;&quot; 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 &quot;SELECT * FROM WRONGTBL FOR XML AUTO&quot;, adoConn Dim aer As ADODB.Error For Each aer In adoConn.Errors Debug.Print &quot;Recordset Object&quot; Debug.Print &quot;Error Description: &quot; & aer.Description Debug.Print &quot;Native Error: &quot; & aer.NativeError Debug.Print &quot;SQL State: &quot; & 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 = &quot;&quot; sQuery = sQuery & &quot;SELECT * FROM WRONGTBL FOR XML AUTO&quot; sQuery = sQuery & &quot;&quot; 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 = &quot;{5D531CB2-E6Ed-11D2-B252-00C04F681B71}&quot; '  Create the output stream to stream the results into. Set outStrm = CreateObject(&quot;ADODB.Stream&quot;) outStrm.Open

'  Set command's output stream to the output stream that you just opened. adoCmd.Properties(&quot;Output Stream&quot;) = 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 &quot;Command Stream Object&quot; Debug.Print &quot;Error Description: &quot; & aer.Description Debug.Print &quot;Native Error: &quot; & aer.NativeError Debug.Print &quot;SQL State: &quot; & aer.SQLState Next On Error GoTo 0 End Sub  Modify the connection string in sConn to reflect the server name and credentials that are used to connect to your SQL Server 2000 server. Note that the SQL statement deliberately attempts to query a nonexistent table (WRONGTBL).</li> 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

</li> 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:

</li></ol>

<div class="references_section">