none
noise words issue SQL 2005

    Question

  • Hey </br> (:

     

    I am using fulltext Search in ms sql 2005, 

     

    I tried to search a specific word, lets  say "IT" in some specific cell in my DB.

     

    I understand that sql have catalog of "noise words", so i read a little on the web and found the txt file of the noise in 

    C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTData\noise...txt

     

    I did tried to remove the IT word from the txt file and even change the file extension, of the language ENG, ENU, SVE.

     

    after the change i tried to REBUILD with "right click" and also with the cmd line:  "alter fulltext catalog FTCat_events rebuild"

     

    My full text Catalog is on specific column on a specific table.

     

    BUT yet, when i run the query in 250k rows table with 110 columns :

    I get answer in 1 sec of none result found, but with other common string (let say SQL)  I can see that the server is real searching.. and got alot of results

     

    Thank you in anticipation

     

     

     

     


    • Edited by Eyal Osher Monday, January 02, 2012 2:50 PM
    Monday, January 02, 2012 2:19 PM

All replies

  • I belleive you ahve alreayd tried these options but just to docuble check.

    • Disable Noise words transformation
    sp_configure 'show advanced options', 1
    RECONFIGURE
    GO
    sp_configure 'transform noise words', 1
    RECONFIGURE
    GO
    
    • After modifying  noise-word file, you have repopulate the full-text catalogs
    • Must have read this article http://support.microsoft.com/kb/905617
    • In non on this works then you might want to give a try to
      EXEC sp_configure 'precompute rank', 1

     


    DBATAG
    MCITP, MCDBA
    Follow UP : Blog ; TWITTER ; FACEBOOK ; YOUTUBE ; ASK a QUESTION

    *** Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    Monday, January 02, 2012 5:45 PM
  • You need to edit the noise word list which pertains to the word breaker you defined in your index of the server default.

    To get the server default issue this query:

    sp_configure 'default full-text language'

    or this:

    select value  from  sys.configurations where name='default full-text language'

    Then using the value returned from above look at your language by this query:

    select * from sys.fulltext_languages

     Then issue a call to sp_help_fulltext_tables to determine the work breaker for your column.

    Then consult this list to determine which file to edit.

    http://office.microsoft.com/en-us/sharepoint-portal-server-it/editing-a-noise-word-file-HA001160402.aspx

    You will find the files here:

    C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTData

    Once you edit the files you will need to rebuild the catalog. This should take care of the problem.

     


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
    Monday, January 02, 2012 10:00 PM
  • DBATAG,thanks,  but i need the fillter still on exept specific words

    Hilary, thanks Alot for the good and detailed explanation , but that is exactly what i did and the problem is that after REBUILD nothing happens.

     

    any other suggestion ?

     

     

    Tuesday, January 03, 2012 2:32 PM
  • You write:

    I did tried to remove the IT word from the txt file and even change the file extension, of the language ENG, ENU, SVE.

    What is language are you querying in, what is your word breaker, and what noise word file did you modify - if you are using Hebrew you should be modifiying the noiseneu.txt and use the neutral word breaker.

     

     


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
    Tuesday, January 03, 2012 3:00 PM
  • my query is coming from magic, and most of them are in hebrew, but the query that blocked is to search for an English word "IT"

    modified the ENG one (according to web advise suggestion, i also changed the ENU.

    for my knowledge - I am not using any word breaker.

    Wednesday, January 04, 2012 1:37 PM
  • what is your default full-text language? is what does this return?

     

    select value  from  sys.configurations where name='default full-text language'

     

    You must edit that noise word list. Also how many instances of SQL Server are on your machine? Are you sure you are modifying the correct noise word list for your instance?

    You will likely not go wrong by editing the IT out of noiseenu.txt, noiseeng.txt and noiseneu.txt.


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
    Wednesday, January 04, 2012 3:55 PM