Tuesday, April 3, 2012

Msg 306, Level 16, State 2, Line 23

You will receive below error when you try to GROUP BY or ORDER BY a column with data type TEXT,NTEXT or IMAGE.

Msg 306, Level 16, State 2, Line 23
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.


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

NTEXT – Data type is used for variable-length of Unicode data
TEXT – Data types is used for variable-length non-Unicode data
IMAGE – Data type is used for variable-length binary data.

The work around for this error is to convert TEXT or NTEXT columns to VARCHAR or NVARCHAR when you use these columns in ORDER BY or GROUP BY clause. You can convert IMAGE columns to VARBINARY.

Example for ORDER BY clause-
Select * from dbo.employee order by comments;

Modify the query as
Select * from dbo.employee order by CAST (comments as nvarchar)

Example for GROUP BY clause-
Select * from dbo.employee group by dept;

Modify the query as
Select * from dbo.employee group by CAST (dept as nvarchar)

Regards,
Satishbabu Gunukula
http://www.sqlserver-expert.com