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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Discover CFTicket - The leading ColdFusion Help Desk and Trouble
Ticket application
http://www.houseoffusion.com/banners/view.cfm?bannerid=48
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:192455
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=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
