0

I am running a full-text search for my site using SQL Server 2008 R2 and freetexttable. I am getting this error when a stop word is entered:

Informational: The full-text search condition contained noise word(s).

So I did what everyone said to do and turned on the Transform Noise Words so the stop/noise words are ignored and the query can continue. But this changed nothing:

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

I still get the same error message. So right now my only option is to turn off the stop list, but that is bad because if someone puts "the" in their search it will return ALL pages because every page pretty much has "the" in it. I want the stop words removed, but SQL Server 2008 R2 is not working for me.

Has anyone actually gotten this to work in 2008 R2? My compatibility level was already 100.

Is there any way for me to get stop words to be ignored and not indexed?

jnl
  • 447
  • 4
  • 7

1 Answers1

2

I appreciate this is very late it might help someone else down the line.

Create a newstoplist based on the system stoplist; this creates a new stoplist with all the words in the system stoplist (which you can't amend in 2008+ sql)

CREATE FULLTEXT STOPLIST slMyNewStopList
FROM SYSTEM STOPLIST AUTHORIZATION dbo;

I then checked the new spotlist was created correctly; 

select * from sys.fulltext_stoplists
select * from sys.fulltext_stopwords

In my case the new stoplist was ID 6. The word I wanted to remove was it; as people wanted to be able to search for "IT Function" in data. So I checked the words in the catalog like this;

select * from sys.fulltext_stopwords
where stoplist_id = 6 and stopword = 'it' and language like '%english%'

To remove a word from the stoplist you need to call a system function; i removed it for English and British English. You have to have the semi colons at this point.

ALTER FULLTEXT STOPLIST slMyNewStopList DROP 'it' LANGUAGE 'English';

ALTER FULLTEXT STOPLIST slMyNewStopList DROP 'it' LANGUAGE 'British English';

I then confirmed the word was missing from my stoplist by running the select query again

select * from sys.fulltext_stopwords
where stoplist_id = 6 and stopword = 'it' and language like '%english%'

I then updated the table I wanted to be able to search for IT words on;

USE [Database]
GO
ALTER FULLTEXT INDEX ON [dbo].[tblDocuments] SET STOPLIST = [slMyNewStopList]

Finally I rebuilt the catalog so the documents were all reindexed. 

USE [Database]
GO
ALTER FULLTEXT CATALOG [catExample] REBUILD
GO
u07ch
  • 13,324
  • 5
  • 42
  • 48