What do you mean my query is broken? Search isn't working? The Fulltext Phantom.

I have a love/hate relationship with 'problems'. I really like troubleshooting, because I learn so much. Sometimes though it can be really frustrating. Luckily (and I'm sure it was mostly luck that something tickled the back of my memory on this one) I was able to quickly resolve this issue.

Ticket:

I can search and find articles with up to 2 words but anything 3 words and beyond fails to return QA results. 

What? OK, first thing is to ask lots of questions, but yes, the data they wanted was there in all environments, and the query should have returned results, and it WAS returning results, but only in Test, not QA.

Here is the query (First the base one, then a modified one, neither worked in QA):

SELECT *

  FROM [MyDB].[this].[table]

  where value like 'diana arriving at the studio%'

******second query*******

USE [MyDB]

GO

DECLARE       @return_value int,

              @RowCount int

EXEC   @return_value = [this].[table]

              @SearchText = N'FORMSOF (INFLECTIONAL,"diana") AND FORMSOF (INFLECTIONAL,"arriving") AND FORMSOF (INFLECTIONAL,"at") AND FORMSOF (INFLECTIONAL,"the") AND FORMSOF (INFLECTIONAL,"studio")',

              @Start = 0,

              @End = 100,

              @RowCount = @RowCount OUTPUT

SELECT @RowCount as N'@RowCount'

SELECT 'Return Value' = @return_value

GO

So one of my first questions is: what's different. So I look through some of the basic configurations and data, and yep all of the data, views, access etc are there. Then I stopped to think. They're doing a search. What's one of the best ways to make searches like this faster? Using Fulltext! So I went to look at the settings for Fulltext on all of the servers (prod, QA, test, etc) and found a difference. Looks like they had Transform Noise Words OFF on the offending servers. So a quick turn on:

sp_configure 'show advanced options', 1;

RECONFIGURE;

GO

sp_configure 'transform noise words', 1;

RECONFIGURE;

GO

And the query is returning results as it should. Kind of a sneaky setting, and if you haven't had exposure to Fulltext you might not think of it. So here's your reminder!