Coldfusion 8 MS SQL 2008 Full Text Search Noise Words (BUG)

[CCJames]

I've been racking my brain over this little gem for well over a day.

Basically, the gist of the matter is that when performing a query against a MSSQL 2008 database in CF 8 (or 8.01) for that matter, results are returned fine UNLESS a noise word is included anywhere within the criteria.  For example, assuming that the text "add-on code" and "addon code" exist in the someOtherField for any number of records:

SELECT someCol

FROM someTable

WHERE someField = someValue AND CONTAINS(someOtherField, ' "add" OR "code" ')

The above query will return as many results as there are matches, however...

SELECT someCol

FROM someTable

WHERE someField = someValue AND CONTAINS(someOtherField, ' "add" OR "on" OR "code" ')

the above query will return zero matches.

SELECT someCol

FROM someTable

WHERE someField = someValue AND CONTAINS(someOtherField, ' "add-on code" ')

the above query will ALSO return zero matches.

If you then take the exact SQL and run it in SQL Management Studio or Query Analyzer you will get results for all queries (again assuming you have matches in the table).

The bug appears to exist within the macromedia bit of what's going on behind the scenes.  I tested this by doing the following:

1. Download the MS JDBC 2.0 driver (http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=99b21b65-e98f-4a61-b811-1991...)
2. Placed the sqljdbc4.jar in my cfusion/lib folder
3. Restarted my CF instance
4. Created a new datasource by choosing "other" from the driver drop down
   1. Named the datasource
   2. Used the following url:  jdbc:sqlserver://xxx.xxx.xxx.xxx;databaseName=someDb;SelectMethod=direct;MaxPooledStatements=1000;
   3. Used the following driver classs:  com.microsoft.sqlserver.jdbc.SQLServerDriver
   4. Filled in the rest of the info, un/pw/description
      
5. Saved the datasource and it connected fine
6. Ran the above queries and ALL returned results

However, the "standard" method for dealing with queries seems to have suffered presumably because the Macromedia logic wasn't intercepting.  What I mean by this is that if I created a batch within a CFQUERY such that I inserted records and then executed a SELECT, the SELECT statement was summarily ignored.

This is due to the fact that "normal" operation of the Macromediafied DSN's automatically get rid of "count information" (same as setting NOCOUNT ON).  Sure enough, if I set NOCOUNT ON for the INSERT query then the select executes as expected.

It's not practical to go back through 10 years of code and set NOCOUNT ON everywhere, so I decided to add Macromedia back into the mix by changing the URL from the what is indicated in the list above to:  jdbc:macromedia:sqlserver://xxx.xxx.xxx.xxx;databaseName=someDb;SelectMethod=direct;MaxPooledStatements=1000; (note the macromedia in the URL).

This fixed my insert/select problem, but NOW the original issue is back.  ANY noise words included in a FTS operation yields ZERO results.

This appears to be a bug within CF 8 when dealing with MSSQL 2008 (don't know about 2005).  Does ANYONE have any insight into how to solve this particular problem.

It appears to be the only real hairy issue at the moment, so any help would be appreciated.

[CCJames]

Here's a little more information that I've discovered.  It would appear that when issuing a query leveraging full text (and specifically the CONTAINS) method against 2008 and including stop/noise words, two result sets come into existence.  The first is blank (always) the second contains the expected results.

I know this because I took the exact query and created a stored procedure out of it.  Then I dumped the first result set and then the second result set.  The second result set had the data I was looking for.  When there aren't any stop/noise words in the criteria, only one result set is returned.

I still think this is a CF issue, because in all the research I've done thus far, I can't find any indication that the SQL server itself is returning any more than one result.  This seems to be supported by the first post as if I bypass the macromedia_drivers, I get the desired result.