I hate unsolved problems floating around on the mailing lists so for the benefit of anyone else who might experience this problem. The solution I found was to use a different JDBC driver. The one I've got running I found at the link below courtesy of Oðuz Demirkapý
http://blog.demirkapi.net/post.cfm/installing-jtds-sql-server-jdbc-driver-fo r-coldfusion The instructions are: - Download the new driver from http://jtds.sourceforge.net In my case the file name was "jtds-1.2.2-dist.zip" - Extract the file and find the jar file such as "jtds-1.2.2.jar". - Copy the jar file (jtds-1.2.2.jar) into ColdFusion lib directory ("/opt/coldfusion8/lib/") - Restart ColdFusion. - Create a new DSN in ColdFusion administrator as selecting "other" and put following information * CF Data Source Name: your data source name * JDBC URL: jdbc:jtds:sqlserver://yourServerName:1433/yourDatabaseName; * Driver Class: net.sourceforge.jtds.jdbc.Driver * Driver Name: Any name that you prefer (I said jTDS) * And the rest is as always your username, password etc. Once I did that I re-ran my query: select title from citem where CONTAINS (title, N'"natural environment in 2060"' ) First on the old datasource: FAIL Second on the jtds: SUCCESS Problem solved :) > -----Original Message----- > From: Edward Chanter [mailto:[email protected]] > Sent: 18 November 2009 11:33 > To: sql > Subject: CF8 / MS SQL Server 2008 Problem with "contains" > > > Greetings, > > I'm hoping to pick your collective brains about a problem I'm seeing with > querying index on MS SQL 2008. We're running a CF8.01 server on linux red > hat and apache, it connects to a MS SQL Server 2008 box. There is a simple > database table called "content" assume for the minute that it has 2 rows, an > id and title. The title is indexed and it has over a thousand rows in it > with titles of articles that are being uploaded. When I run the following > query in MS SQL Management Studio 2008: > > select title > from content > where CONTAINS (title, '"how to be headhunted"' ) > > It returns 1 result which is correct. > > I then run the same exact query through the CF8 server it returns no results > at all. I've tried it on various different databases on the server and the > result is always the same. > > I'm assuming there must be something in the JDBC driver that is causing this > but I've been searching and I can't find any reports of anyone else having > the same problem. > > best wishes, > > -= Ed > > -------------------------------------------- > Edward Chanter > Technical Innovations Manager > Collaborative Connections Ltd. > > http://www.cc.uk.com/ > [email protected] > Landline: 01273 832221 > Mobile: 07866 365282 > YIM!: jumperb > MSN: [email protected] > You can find me on FaceBook too! > -------------------------------------------- > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ > ~~~~~~~~~~| > Want to reach the ColdFusion community with something they want? Let > them know on the House of Fusion mailing lists > Archive: > http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3252 > Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm > Unsubscribe: > http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=8778.800 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3253 Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.6
