Search

Monday, December 3, 2012

Msg 306, Level 16, State 2, Line 23

You will receive below error when you try to ORDER BY or GROUP BY a column with data type Image, Text, Ntext.

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.


Image – Data type is used for storing variable length binary data.
Ntext – Data type is used for storing variable length of Unicode data.

Text – Data types is used for storing variable length non-Unicode data.

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

The solution for this type of error is to convert Image column to VarBinary, Text column to VarChar and Ntext column to nVarChar when you use these columns in ORDER BY or GROUP BY clause. 

Example for GROUP BY clause:
SELECT * FROM dbo.CustomerMaster GROUP BY CustomerType;

Modify the query as 
SELECT * FROM dbo.CustomerMaster  GROUP BY CAST (CustomerType as nvarchar)


Example for ORDER BY clause:
SELECT * FROM dbo.CustomerMaster ORDER BY CustomerName;


Modify the query as 
SELECT * FROM dbo.CustomerMaster ORDER BY CAST(CustomerName as nVarChar)

No comments:

Post a Comment