PRB: Run-Time Error When You Use SQLXML 2.0 with ADO to Execute an Updategram (323920)


The information in this article applies to:

  • Microsoft SQL Server 2000 (all editions)
  • ActiveX Data Objects (ADO)
  • Microsoft Visual Basic Enterprise Edition for Windows 6.0
  • Microsoft Visual Basic Professional Edition for Windows 6.0
This article was previously published under Q323920

SYMPTOMS

If you use Microsoft SQLXML 2.0 with ActiveX Data Objects (ADO) to execute an updategram that specifies an XML Schema definition language (XSD) schema, and the SQLXML Version property of the connection object is set to a default value, you receive the following error message in Microsoft Visual Basic 6.0:
Run-time error '-2147217887 (80040e21)':
Specified attribute or element ('ElementName') does not have a corresponding mapping in the schema, and no overflow field defined.

CAUSE

This behavior occurs because the SQLXML Version property is not set to "SQLXML.2.0".

By default, the SQLXML Version property is set to the default value when you use the Microsoft OLE DB Provider for SQL Server (SQLOLEDB). The default value of this property uses the SQLXML feature that the Sqlxmlx.dll file provides. This .dll file is included with SQLXML 1.0 and Sqlxml2.dll is included with SQLXML 2.0.

WORKAROUND

To use the Sqlxml2.dll file with SQLOLEDB, you must set the SQLXML Version property to "SQLXML.2.0". To do so, uncomment the following line from the Visual Basic code in step 2 of "Steps to Reproduce the Behavior" in the "More Information" section.
conn.Properties("SQLXML Version") = "SQLXML.2.0"

MORE INFORMATION

Steps to Reproduce the Behavior

  1. Save the following XSD schema to your disk as EmpSchema.xml. Update the path that is specified in the code to where you saved the mapping schema. In this example, the schema is saved in C:\Schemas. 
    <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
            xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
  <xsd:element name="Emp" sql:relation="Employees" >
   <xsd:complexType>
        <xsd:attribute name="EmpID"  
                       sql:field="EmployeeID" 
                       type="xsd:string" /> 
        <xsd:attribute name="LName"  
                       sql:field="LastName"  
                       type="xsd:string" />
    </xsd:complexType>
  </xsd:element>
</xsd:schema>
  2. Use the following Visual Basic code to execute an updategram that has an associated mapping schema. The updategram updates the employee last name for employee "1" in the Employees table in the Northwind database. You must provide your user ID (uid) and password (pwd) in the connection string: 
    Private Sub Form_Load()
    Dim cmd As New ADODB.Command
    Dim conn As New ADODB.Connection
    Dim strmIn As New ADODB.Stream
    Dim strmOut As New ADODB.Stream

    ' Open a connection to the SQL Server.
    conn.Provider = "SQLOLEDB"
    conn.Open "server=(local); database=Northwind; uid=UserName; pwd=UserPassword;"
    ' conn.Properties("SQLXML Version") = "SQLXML.2.0"
    Set cmd.ActiveConnection = conn
    
    ' Open the command stream and write the template to it.
    strmIn.Open
    strmIn.WriteText "<ROOT xmlns:updg='urn:schemas-microsoft-com:xml-updategram' >"
    strmIn.WriteText "  <updg:header>"
    strmIn.WriteText "      <updg:param name='EmployeeID'/>"
    strmIn.WriteText "      <updg:param name='LastName' />"
    strmIn.WriteText "  </updg:header>"
    strmIn.WriteText "  <updg:sync mapping-schema='C:/Schemas/EmpSchema.xml' >"
    strmIn.WriteText "      <updg:before>"
    strmIn.WriteText "          <Emp  EmpID='1' />"
    strmIn.WriteText "      </updg:before>"
    strmIn.WriteText "      <updg:after>"
    strmIn.WriteText "          <Emp  LName='Fuller'/>"
    strmIn.WriteText "      </updg:after>"
    strmIn.WriteText "  </updg:sync>"
    strmIn.WriteText "</ROOT>"
    
    ' Set the command dialect to XML.
    cmd.Dialect = "{5d531cb2-e6ed-11d2-b252-00c04f681b71}"
    strmIn.Position = 0
    Set cmd.CommandStream = strmIn
 
    ' Execute the command, open the return stream, and read the result.
    strmOut.Open
    strmOut.LineSeparator = adCRLF
    cmd.Properties("Output Stream").Value = strmOut
    cmd.Execute , , adExecuteStream
    strmOut.Position = 0
    Debug.Print strmOut.ReadText
    strmOut.Close
    strmIn.Close
    conn.Close
End Sub
    When you run this code from Visual Basic 6.0, you receive the error message that is described in the "Summary" section.

REFERENCES

For more information about updategrams, see the following SQLXML 2.0 documentation article "Executing an Updategram Using ADO" for an equivalent XML-Data Reduced (XDR) schema:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqlxml2/updategram_2icv.asp

For more information about how to retrieve XML documents by using FOR XML on the client side, see the SQLXML 2.0 documentation article, "Architecture of Client-Side and Server-Side XML Formatting:"

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqlxml2/clientsidexml_70bp.asp

Modification Type:MajorLast Reviewed:12/5/2003
Keywords:kberrmsg kbprb KB323920 kbAudDeveloper
©2003 Microsoft Corporation. All rights reserved.