caml query error: The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator

Question

When doing a orderby by for a sharepoint field. The error below is generated:

The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

The CAML query orderby clause is as follows:

<Where>
  <And>
    <And>
      <Eq>
        <FieldRef Name='ContentType' />
        <Value Type='Choice'>Documents</Value>
      </Eq>
      <Eq>
        <FieldRef Name='UserID' />
        <Value Type='Text'>UserID_VAL</Value>
      </Eq>
    </And>
    <Neq>
      <FieldRef Name='Email' />
      <Value Type='Text'>NA</Value>
    </Neq>
  </And>
</Where>
<OrderBy>
  <FieldRef Name='Description' Ascending='TRUE'  Nullable='TRUE'/>
</OrderBy>

The field being sorted was Description and it has the following data type in the list Description Single line of text.

Any solutions to this problem.

Thanks in advance.

Ashutosh Singh-MVP SharePoint Ashutosh Singh-MVP SharePoint · Accepted Answer · 2011-01-11T07:08:26

Caml queries that try to sort using multiline columns (Note) will and should produce the following error:

"The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator."

SharePoint MultiLine or Note columns are stored in the AllUserData table in the content database as ntext data types. SQL Server is unable to sort using either text, ntext or image data types. The only way around this is to not use the sorting functionality in CAML and sort the returned dataset yourself.

http://social.msdn.microsoft.com/forums/en-US/sharepointdevelopment/thread/c7a1e8b7-62c0-4b90-8a9b-cc7e3fa6940c

caml query error: The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator

1 Answers