KB45329: "The text data type cannot be selected as DISTINCT because it is not comparable." odbc error is given when running a report against a SQL Server database in MicroStrategy Intelligence Server 9.4.x - 10.x

Salesforce Solutions Architect • Strategy

This technical note outlines an error that occurs when running a report against a SQL Server 2005 or newer database

SYMPTOM:
When executing a report against the SQL Server 2005 (or newer) database in Strategy Intelligence Server 9.4.x - 10.x, the following error can appear:


QueryEngine encountered error: Execute Query failed.

Error type: Odbc error. Odbc operation attempted: SQLExecDirect. The text data type cannot be selected as DISTINCT because it is not comparable.

CAUSE:
The error is thrown by the SQL Server database in response to a DISTINCT select on an Attribute ID column that uses Text data type, as seen below in the Strategy Attribute Editor.

"DISTINCT", is used in the report SQL because the ID form of an attribute needs to be a column that uniquely identifies each attribute element. The Text datatype was deprecated for Varchar(max) in the SQL Server 2005 release.

ACTION:
There are two options available to resolve this issue:

Within the SQL Server database, change the data types of the column(s) with the Text data type to VARCHAR(MAX).
In the Attribute editor, use custom SQL in the attribute form's definition with the CAST or CONVERT function to convert the text data type to VarChar.

Comment

0 comments

Details

Knowledge Article

Published:

June 1, 2017

Last Updated:

June 1, 2017