Text, ntext, and image data types cannot be compar

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Text, ntext, and image data types cannot be compar

Author

Topic

CoffeeAddict
Yak Posting Veteran

94 Posts

Posted - 2010-06-24 : 11:51:20

I don't get it

select e.createdOn,
e.sessionID,
e.detail,
e.[message],
e.page,
e.[path],
e.referer
from ErrorLog e (nolock)
group by e.page,
e.createdOn,
e.sessionID,
e.[path],
e.detail,
e.referer
order by createdOn desc

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

It's not liking the detail or message fields as they are type text. So I took both those out of the group by and still get this error. So you also have to take them out of the select statement completely!?! It's complaining that I still have them in the select even though I removed them from the group by.

I hate you SQL :),

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-06-24 : 12:08:40

You can't take them out of the GROUP BY clause and keep them in the SELECT clause without wrapping them in an aggregate function (which won't work on them anyway). No point in hating SQL, that's how it's supposed to work.

Could you just use:

select distinct e.createdOn,
e.sessionID,
e.detail,
e.[message],
e.page,
e.[path],
e.referer
from ErrorLog e (nolock)
order by createdOn desc

CoffeeAddict
Yak Posting Veteran

94 Posts

Posted - 2010-06-24 : 13:06:56

I still hate SQL. Most Developers do :).

I needed an order by, not a grouping. duh. Resolved.

Kristen
Test

22859 Posts

Posted - 2010-06-24 : 13:48:01

"I still hate SQL"

From where I am sitting here's my two-penny-worth:

Get rid of those datatypes - they were deprecated in SQL2005. VARCHAR(MAX) will behave much more nicely than TEXT ever did ...

Don't use NOLOCK. HUGE danger is using that; its most probably biting you in the arse with unrepeatable "weird happenings", if not it will do - maybe tomorrow, if not then "soon"

.

If you are trying to avoid READS-blocking-WRITES then use READ_COMMITTED_SNAPSHOT instead - you just set that on the database, you don't need any code changes (other than to take out NOLOCK wherever it is in your code - unless your code specifically handles the issues NOLOCK raises)

I appreciate this next is an answer to what your question said, rather than what you wanted, and which you have now solved

but in case useful next-time-around:

If you need to use a GROUP BY for Aggregated / "Grouping" / etc but you want columns that are NOT in the GROUP BY LIST then that can usually be achieved by using ROW_NUMBER ... OVER. If/when you have that need then stick the problem in a post here ... someone will enjoy the mental challenge of working out how to get ROW_NUMBER ... OVER to do exactly what you want

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources