SAP Knowledge Base Article - Public

1325928 - Adding Memo field gives The ntext data type cannot be selected because it is not comparable error

Symptom

When you add a memo fields to an existing report, youreceive an error of "Failed to Open a rowset". Then it displays: Failed to Open a rowset, DETAILS: ADOErrorCode: Ox80040e14, SOURCE: Micorsoft OLEDB Provider for SQL Server, DESCRIPTION: The ntext data type cannot be selected because it is not comparable. SQLSTATE:42000, Native error: 421.

ENVIRONMENT DETAILS:

  • Crystal Reports 2008 V 1
  • Reporting Database: MS SQLSERVER 2005

Reproducing the Issue

  1. Create a Crystal Report against MSSQLSERVER 2005 using the native oledb connection
  2. Choose Select Distinct in Report Options.
  3. Try to place a memo field on the report-
  4. in MsSQLSERVER the field is defined as an ntext type
  • you will get an error:
  • The ntext data type cannot be selected because it is not comparable. SQLSTATE:42000, Native error: 421
  • you will not be able to do anything else until you remove the field from the report

Cause

Select Distinct cannot be used with this type of field- MSSQLSERVER does not allow it:

Resolution

Solution 1: 

Uncheck the Select Distinct on the database tab in Crystal Reports

Solution 2 -if you need the Select Distinct

 Create a Stored Procedure on the Database and cast the ntext field as a varchar

See Also

http://social.technet.microsoft.com/Forums/en-US/sqlexpress/thread/6e5fc605-3dd5-4de6-bf69-5c16587117df/

Keywords

The ntext data type cannot be selected because it is not comparable,SQLSTATE:42000, Native error: 421, memo field, Crystal Reports 2008, ntext , KBA , BI-RA-CR , Crystal Reports designer or BusinessViews Manager , Problem

Product

Crystal Reports 2008 V1