SQL Server Error Message :

SQL Server Error Msg 421, Level 16, State 1: The ntext data type cannot
be selected as DISTINCT because it is not comparable.

Cause :

The text, ntext and image are fixed and variable-length data types used for storing large non-Unicode and Unicode character and binary data. The text data type is used for variable-length non-Unicode data in the code page of the server and with a maximum length of 2,147,483,647 (2^31 – 1) characters.

The ntext data type is used for variable-length Unicode data with a maximum length of 1,073,741,823 (2^30 – 1) characters. The image data type is used for variable-length binary data from 0 to 2,147,483,647 (2^31 – 1) bytes.

Solution :

For SQL Server 2005 and SQL Server 2008 (and later), instead of limiting the NVARCHAR to 4000 characters or the VARCHAR or VARBINARY to 8000 characters, the MAX specifier can be used in its place, as can be seen in the following SELECT statements:

SELECT DISTINCT [BookTitle], CAST([BookSummary] AS NVARCHAR(MAX))
AS [BookSummary]
FROM [dbo].[Book]

SELECT DISTINCT [BookTitle], CAST([BookImage] AS VARBINARY(MAX))
AS [BookImage]
FROM [dbo].[Book]

Source : sql-server-helper.com

 

 

Leave a comment

comments