Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Full Text Search Noise Words Problem in Application

Posted on 2005-03-07
Medium Priority
Last Modified: 2008-01-16
I'm developing an ASP.NET application (in C#) that uses MS SQL Server.  I am using Full Text Searches to do the searching within the application.  Eveything works fine, but the problem is that when a user enters a word that appears in SQL's noise words file, SQL returns an error stating that a search was submitted that contained only a noise word:

Execution of a full-text operation failed. A clause of the query contained only ignored words.
I’ve looked this up online, and apparently many people encounter this problem.  The solutions I’ve seen online suggest:
a)       Removing the list of noise words from the noise.eng file.
b)       Filtering these words out client side (any time before they get to the DB).
Option A won’t work because there are several other applications (including Commerce Server) that use the same database server, so, I can't just change the noise.eng file.  Besides, the noise words are supposed to be filtered out, so ideally it would be good to keep the noise words defined but just have them be filtered.  
Option B could work, but it would require building a mirror of the noise.eng file, and filtering every single noise word against every single search term that is entered (which means breaking each of the 40 multi-word search terms entered on the advanced search page out into arrays of individual words, and comparing each word versus every single noise word).  That seems like it would be a lot of work to code, and more importantly, it seems like it would have a terrible impact on performance.

I could just trap the error that SQL returns, but the problem is that the error does not indicate which search term was invalid, so I can't provide meaningful feedback to the user.  

Is there a standard way that people deal with this problem?  Is there existing code out there somewhere (Javascript, C#, etc). that provides the filtering functionality against the default SQL noise words list?

Any help would be greatly appreciated.  Thanks.
Question by:PvtJoker670
  • 4
  • 3
  • 2
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13478576
>>Is there a standard way that people deal with this problem?  <<
The way I have done it in the past is your Option C.  Namely, trap the 7619 error.  It is by far the simplest approach.

Author Comment

ID: 13479219
Thanks for the response.  In that case, what message do you display to the user?  Since the error does not mention which search term caused the problem, how does the user know how to fix his or her search?
LVL 75

Accepted Solution

Anthony Perkins earned 500 total points
ID: 13479287
The simple answer is you can't.  Don't get me wrong, it is not that I do not understand the problem, you have very clearly described all the options available.  Until we have a better tool all we can do is choose the least offensive.
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.


Author Comment

ID: 13480126
Ok, thanks for the help!  I basically wanted to make sure I wasn't missing something :-).  

Expert Comment

ID: 13481070

I found this item on the page:


Q: What's the best way to work with the noise words issue - you don't want your users to see "contained only ignored words" - is there a way to just tell SQL to ignore JUST THOSE words and use the rest?

A: Full-text maintains a list of noise words in different files which are language specific. When you receive the Msg 7619 “The query contained only ignored words" SQL server has eliminated all words in the query. If you would like to search on those words you can try utilizing FREETEXT or editing the files maintaining the list of noise words.

Have you taken a look at FREETEXT?

Author Comment

ID: 13481762
Thanks for the additional information, Lozza.  I'll definitely check that out.  

I noticed the following article:


Query #4 in that article seems to indicate that FREETEXT will return the 7619 error when only a noise word is entered as well.  I'll try to check it out tomorrow...

Expert Comment

ID: 13486405

Also found this KB article:

It seems to suggest that SP1 fixed an issue where error 7619 would not set @@error correctly.

If you are running higher than SP1, have you tried writing a stored proc wrapper, that will return a clean error message if 7619 is returned from the fulltext query?

My understanding of the error message is that all terms within the expression are noise words, rather than just some- that you may want to identity to the user.

Is this correct, or has your experience shown this not to be the case?


Author Comment

ID: 13499464
Yes, I am running higher than SP1, and yes, I'm planning on wrapping this in a sproc and just returning the generic error message to the user.  My problem is that while this will be a "clean" error message, it won't tell them which of the terms they entered is causing the problem.  But, it looks like this is just the way it works, and we have to live with it :-).

I experimented with freetext() (as opposed to CONTAINS()), and I found that most of the time it will still return the 7619 error just like CONTAINS().  However, with single letters 'a, 'b', etc., the error was not returned.

Thanks again for all the input!

Expert Comment

ID: 13504336

Pleasure. I found this issue interesting myself.
Good luck.

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question