|
|
Error Message
Server: Msg 420, Level 16, State 1, Line 1
The text, ntext, and image data types cannot be used
in an ORDER BY clause.
Causes:
As the error message suggests, you cannot use a column of text, ntext or image data type as part of the ORDER BY clause in a SELECT statement.
|
To illustrate, let’s assume that you have the following table in your database:
CREATE TABLE [dbo].[Notes] (
[NotesID] INT NOT NULL IDENTITY(1,1),
[NotesOwner] VARCHAR(20),
[NotesText] TEXT )
You want to get all the records from the [dbo].[Notes] table but instead of sorted by the notes ID (NotesID) or notes owner (NotesOwner), you want the result be sorted by the notes text (NotesText).
SELECT * FROM [dbo].[Notes]
ORDER BY [NotesText]
Issuing this SELECT statement will generate the following error message:
Server: Msg 420, Level 16, State 1, Line 1
The text, ntext, and image data types cannot be used in an ORDER BY clause.
Solution / Work Around:
One way to work around this limitation is to CAST or CONVERT the TEXT/NTEXT data type column into either VARCHAR or NVARCHAR data type.
SELECT * FROM [dbo].[Notes]
ORDER BY CAST([NotesText] AS VARCHAR(8000))
If the column is of NTEXT data type, you can CAST it to NVARCHAR, but with only a length of 4000 since this is the maximum length allowed by a NVARCHAR data type:
SELECT * FROM [dbo].[Notes]
ORDER BY CAST([NotesText] AS NVARCHAR(4000))
|