The error message is correct... "ntext" (and "text") fields cannot be used the same as varchar (or char) fields... that's part of what makes them different. ("text" fields can be huge, and are not stored in a way that is convenient for SQL Server to do the normal kinds of things like grouping and sorting.)
If you need ntext then you need to change your query. Luckily that's not too hard... Here is a method that uses a nested SELECT (doing the GROUP BY) as a table (aliased to a "table" named "t1") *within* the FROM clause. I can't run this (obviously) so it may have a syntax error or something, but it ought to work... SELECT h3_articles.ARTICLE_ID, h3_articles.TITLE, h3_articles.ARTICLE, h3_articles.CATEGORY_ID, h3_articles.DATE_POSTED, h3_articles.POSTED_BY, h3_articles.TIME_POSTED, h3_categories.CATEGORY_ID, h3_categories.CATEGORY, h3_categories.CATEGORY_ORDER, t1.NumComments from (select h3_articles.ARTICLE_ID, count(h3_comments.ARTICLE_ID) as NumComments FROM (h3_articles LEFT OUTER JOIN h3_comments ON (h3_articles.ARTICLE_ID = h3_comments.ARTICLE_ID)) GROUP BY h3_articles.ARTICLE_ID ) as t1 INNER JOIN h3_articles ON (h3_articles.ARTICLE_ID = t1.ARTICLE_ID) ORDER BY h3_articles.DATE_POSTED desc, h3_articles.TIME_POSTED desc -----Original Message----- From: Howard Owens [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 04, 2002 4:57 PM To: CF-Talk Subject: (OT - SQL) Changing Data Type Screws up Query I want to change the data type on one of my columns. I want to change it from varchar to ntext. On my development box, everything works great except one query that has me stuck. Here's the error message I'm getting: ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC SQL Server Driver][SQL Server]The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator. But if I try removing the column name from the GROUP BY statement, I get this error: ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC SQL Server Driver][SQL Server]Column 'h3_articles.ARTICLE' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. I just don't know SQL well enough to know where to take it from here. Here's my Query: SELECT h3_articles.ARTICLE_ID, h3_articles.TITLE, h3_articles.ARTICLE, h3_articles.CATEGORY_ID, h3_articles.DATE_POSTED, h3_articles.POSTED_BY, h3_articles.TIME_POSTED, h3_categories.CATEGORY_ID, h3_categories.CATEGORY, h3_categories.CATEGORY_ORDER, count(h3_comments.ARTICLE_ID) as NumComments FROM (h3_articles INNER JOIN h3_categories ON h3_articles.CATEGORY_ID = h3_categories.CATEGORY_ID) LEFT OUTER JOIN h3_comments ON (h3_articles.ARTICLE_ID = h3_comments.ARTICLE_ID) GROUP BY h3_articles.ARTICLE_ID, h3_articles.TITLE, h3_articles.ARTICLE, h3_articles.CATEGORY_ID, h3_articles.DATE_POSTED, h3_articles.POSTED_BY, h3_articles.TIME_POSTED, h3_categories.CATEGORY_ID, h3_categories.CATEGORY, h3_categories.CATEGORY_ORDER ORDER BY h3_articles.DATE_POSTED desc, h3_articles.TIME_POSTED desc Suggestions, hints, directions??? H. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting.