?
Solved

Full Text Search Noise Words Problem in Application

Posted on 2005-03-07
9
Medium Priority
?
364 Views
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.
0
Comment
Question by:PvtJoker670
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
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.
0
 

Author Comment

by:PvtJoker670
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?
0
 
LVL 75

Accepted Solution

by:
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.
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

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

Expert Comment

by:lozzamoore
ID: 13481070

I found this item on the page:

http://www.microsoft.com/technet/community/chats/trans/sql/sql0708.mspx

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?
Cheers,
0
 

Author Comment

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

I noticed the following article:

http://www.windowsitpro.com/SQLServer/Article/ArticleID/8967/8967.html

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...
0
 
LVL 7

Expert Comment

by:lozzamoore
ID: 13486405

Also found this KB article:
http://support.microsoft.com/default.aspx?scid=kb;en-us;287167

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?

Thanks,
0
 

Author Comment

by:PvtJoker670
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!
0
 
LVL 7

Expert Comment

by:lozzamoore
ID: 13504336

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

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

764 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