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!
