3

I've moved a database from SQL Server 2000 to SQL Server 2014 via an import/export through SQL Server 2008 to make it compatible to be imported in 2014. So far that has worked fine.

I found out that using words which are usually in a stop list (like articles) disrupts or breaks the retrieval in a FullText search and return no results. Using only the important keywords returns the result as usual. AFAIK in older versions of SQL Server a different system than stop list were used. Although I can not find anything relevant in the FTData folder because it is either empty or commented.

On inspecting the database via SQL Server Management Studio I see that no stop list is configured in the selected database (under Storage). I tried adding a new one based on the system stop list but it made no difference to the behavior.

What can cause this behavior?

UPDATE

The used query:

SELECT ProductID FROM SearchTable WHERE CONTAINS(*, '"word"') AND CONTAINS(*, '"noiseword"')

The results fail because in the search query the words are connected with AND. Using a noise word apparently returns always false so no item is returned.

I tried to change that behavior but it is not prevented by this:

sp_configure 'show advanced options', 1; RECONFIGURE; GO sp_configure 'transform noise words', 1; RECONFIGURE; GO

I will try a rebuild which may help based on this and this article, as suggested by wBob.

Improve this question
3
  • 1
    You might be better off dropping the existing full-text index and rebuilding from scratch in 2014 to see if you get a different behaviour. You should also try running some tests with sys.dm_fts_parser eg something like this: SELECT * FROM sys.dm_fts_parser( '"test a n noise the word"', 1033, 0, 0 ) Commented Apr 12, 2016 at 15:08
  • I see that only English words are filtered, however the used language is German. Can I change that? Commented Apr 12, 2016 at 18:29
  • LANGUAGE is one of the arguments of the CREATE FULLTEXT INDEX command. I suggest you have a good read over this article. Commented Apr 12, 2016 at 22:05

1 Answer 1

Reset to default
7

Simply deactivating the stop list fixed the problems. There may be better solutions but this worked for me.

Use database; ALTER FULLTEXT INDEX ON table SET STOPLIST OFF; GO
Improve this answer
1
  • This thing works. But why tf this thing work? My stoplist is empty why i have to disable it? Wirdo. Commented Jul 22 at 11:11

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.