In order for memo fields to be included, you need to set the KeyFieldList property of the cursor using Cursorsetprop(). This has another effect - the before and after list will only include changed fields, not all fields, once you set KeyFieldList.
>
>I haven't a clue why memo fields are left out unless you set Keyfieldlist...


Hi,

How do I address the following when using XMLUpdategrams with memo/text fields with SQL Server 2000:

OLE IDispatch exception code 0 from Microsoft OLE DB Provider for SQL Server: The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator...

Everything works fine if:
1) I don't edit the Notes field in any of the records
2) I manually remove the Notes entry in the 'before' section of the XMLUpdateGram


Thanks,

- Brian


Below is my test code:

* Make sure you've added the following to ado.h to use ADO.Streams
*    #define   adExecuteStream   0x400

#INCLUDE ado.h


*-- Initialize vars
LOCAL ;
	oADOConn AS ADODB.Connection, ;
	oADOCmd AS ADODB.Command, ;
	strmXMLOut AS ADODB.Stream, ;
	strmXMLIn AS ADODB.Stream, ;
	strXML AS String

*-- Create connection
oADOConn = CREATEOBJECT("ADODB.Connection")
oADOConn.Mode = 3
oADOConn.ConnectionString = 'Provider=SQLOLEDB.1;' ;
	+ 'Persist Security Info=False;' ;
	+ 'Integrated Security=SSPI;' ;
	+ 'Initial Catalog=Northwind;' ;
	+ 'Data Source=(local)'

*-- Create command object
oADOCmd = CREATEOBJECT("ADODB.Command")

*-- Open the connection
oADOConn.Open

*-- Set connection
oADOCmd.ActiveConnection = oADOConn


*-- Set output to receive XML stream
strXML = ""
strmXMLOut = CREATEOBJECT("ADODB.Stream")
strmXMLIn = CREATEOBJECT("ADODB.Stream")

oADOCmd.Properties("Output Stream") = strmXMLOut
oADOCmd.Properties("Output Encoding") = "UTF-8"
oADOCmd.CommandText = "SELECT * FROM employees FOR XML AUTO, BINARY BASE64"

*-- Get the XML stream
strmXMLOut.Open
oADOCmd.Execute(,,adExecuteStream)
strXML = strmXMLOut.ReadText
strXML = "<XML>" + strXML + "</XML>"
STRTOFILE(strXML, "XMLOutStream.txt")  && Helpful to see what SQL Server returned

*-- Create cursor and set buffering so that an updategram can be created
XMLTOCURSOR(strXML,"csrTemp")
CURSORSETPROP("Buffering",5,"csrTemp")

*-- Set KeyFields  -- mucho importante!
CURSORSETPROP("KeyFieldList","employeeid","csrTemp")

BROWSE	&& Edit the Notes field for employeeID=1

*-- Create an XML Update file and string
strXML = XMLUPDATEGRAM("csrTemp",0+8+32)
strXML = STRTRAN(strXML,"csrtemp","employees")
STRTOFILE(strXML,"XMLUpdate.xml")

*-- Update SQL Server
strmXMLIn.Open
strmXMLIn.Charset = "UTF-8"
strmXMLIn.WriteText = strXML
strmXMLIn.Position = 0
oADOCmd.CommandStream = strmXMLIn

*-- If the notes field was one of the modified fields
* this will give the error:
*   "...The text, ntext, and image data types cannot be compared
*   or sorted, except when using IS NULL or LIKE operator..."

oADOCmd.Execute(,,adExecuteStream)


SUSPEND
CLOSE TABLES ALL