April 4th, 2011, 03:01 AM
CFquery shows no results when using a phrase and fulltext indexing
I have an odd issue with CFQUERY Running a dynamicly created SQL script which includes in this case a phrase surrounded in quotes and using Full Text indexing on SQL 2008.
When I search with a keyword - no problems. When I search with a phrase (using double quotes) I then get 0 results. When I output the query thats produced and cut and paste directly into the server and run from there then I get results.
For some reason CFQUERY is not getting the results. Is it something to do with double quotes? - the query outputs fine and this used to work and does work on my development machine in SQL 2000. However, the live server was upgraded to 2008 not long ago and this has happened.
I have also tried creating a query using Freetexttable but no difference.
Any help would be extremely helpful.
April 4th, 2011, 09:03 AM
It could be the quotes. Can you run the exact same SQL statement in the SQL Server Query tool?
You might look at using cfqueryparam, try the PreserveSingleQuotes() function, or if necessary add some code to try removing, replacing, or escaping the quotes in the search string.