Monday, June 11, 2012

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

SQL Server Error Message :
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

In order to compare or sort text/ntext, you need to convert it to varchar (or similar datatype that can allow compare/sort). Note, text/ntext often has a large capacity for data than varchar.


Example SQL Code 1:
[...] ORDER BY TableColumn
change to
[...] ORDER BY cast(TableColumn as varchar(500))


Example SQL Code 2:
[...] GROUP BY TableColumn
change to
[...] GROUP BY cast(TableColumn as varchar(500))



6 comments:

Best Business Brands said...

ntext, text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to ...

GuildWars2Items said...

A bumblebee if dropped into an open tumbler will be there until it dies, unless it is taken out aion gold, it never sees the means of escape at the top, but persists in trying to find some way out through the sides near the bottom aion gold, It will seek a way where none exists, until it completely destroys itself aion kinah.

The past is gone and static. Nothing we can do will change it.scarlet blade gold, the future is before us and dynamic. Everything we do will affect it rs gold, You laugh at mescarlet blade gold for being different , but I laugh at you for being the same.

Sudhir DBAKings said...

Nice post. Helps a lot. Thanks for your post.

http://dbakings.com/sqldatabase/Default.aspx

Sudhir DBAKings said...

Nice post. Helps a lot. Thanks for your post.

www.dbakings.com/sqldatabase/Default.aspx

Hariharan s said...

Thanks.i helped me a lot in referring these data types.

http://www.theosoftindia.com/

udhaya kumar said...

I got a job by saying this answer in my last interview. thanks for awesome help.
I got more idea about oracle from Besant Technologies. If anyone wants to get oracle Training in Chennai visit Besant Technologies.
http://www.oracletraininginchennai.in