my initial thoughts are that b.comment is a field of type ntext or text.
This afaik cannot be LIKE compared against, you need to enable and use
free text searching instead
On Mon, 31 Jan 2005 16:49:52 -0500, Rick Root <[EMAIL PROTECTED]> wrote:
> So I'm testing BlogCFM on SQLServer and I've come across this error:
>
> ----------------------------------------------------------------------
> Error Executing Database Query.
> [Macromedia][SQLServer JDBC Driver][SQLServer]The text, ntext, and image
> data types cannot be compared or sorted, except when using IS NULL or
> LIKE operator.
>
> 1057 : OR B.COMMENT LIKE
> <cfqueryparam cfsqltype="cf_sql_varchar"
> value="%#criteria#%">
> ----------------------------------------------------------------------
>
> The entire query is (sorry for the formatting, it's debugger output)
>
> SELECT A.ENTRY_ID, A.USER_ID, A.GALLERY_ID, A.UNAME, A.ENTRY_DATE,
> A.TITLE, A.SUBTITLE, A.ABSTRACT, A.CONTENT, A.FLAG_ALLOW_COMMENTS,
> A.ENTRY_FILENAME, C.CATEGORY_ID, D.CATEGORY_NAME, COUNT(B.COMMENT_ID) AS
> totalComments FROM ((BLOG_ENTRIES A LEFT JOIN BLOG_COMMENTS B ON
> A.ENTRY_ID=B.ENTRY_ID AND A.BLOG_ID=B.BLOG_ID) left join BLOG_CATEGORIES
> C ON A.ENTRY_ID=C.ENTRY_ID AND A.BLOG_ID=C.BLOG_ID) LEFT JOIN CATEGORIES
> D ON C.CATEGORY_ID=D.CATEGORY_ID AND C.BLOG_ID=D.BLOG_ID WHERE
> A.BLOG_ID= (param 1) AND A.ENTRY_DATE >= {ts '2005-01-01 00:00:00'} AND
> A.ENTRY_DATE <= {ts '2005-01-31 23:59:59'} GROUP BY A.ENTRY_ID,
> A.USER_ID, A.GALLERY_ID, A.UNAME, A.ENTRY_DATE, A.TITLE, A.SUBTITLE,
> A.ABSTRACT, A.CONTENT, A.FLAG_ALLOW_COMMENTS, A.ENTRY_FILENAME,
> C.CATEGORY_ID, D.CATEGORY_NAME ORDER BY A.ENTRY_DATE DESC
>
> I think the problem here is that I'm including the text fields in the
> GROUP BY clause... which I have to do in order to get my count of the
> number of comments...
>
> This query works wonderfully in MySQL and MS Access. Seems odd that I
> wouldn't be able to do it in SQL Server.
>
> - Rick
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Find out how CFTicket can increase your company's customer support
efficiency by 100%
http://www.houseoffusion.com/banners/view.cfm?bannerid=49
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:192462
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
