20091028

SQL Server 2005 Full Text Search FTS Thesaurus tsENU.xml issues

Been spending time reading up on SQL Server 2005 FTS Thesaurus files here is what I've learned:
  1. You have to make your own Thesaurus file, can't seem to find one with common expansions.
  2. When you have finished your new file, you need to restart SQL 2005 (if it's 2008 there's a stored procedure to reload it "EXEC sys.sp_fulltext_load_thesaurus_file 1033;" 1033 for the language in this case ENU (US English).
  3. Make sure your search terms are not surrounded by double quotes because you thought they were necessary from a previous hacked together project using SQL FTS.
  4. Run tests and query comparisons to make sure it's working the way you think it should.
  5. Wonder about all the other predicates you can add to your search terms because you can't find a definitive list. 
    FREETEXT(<FieldNames>,formsof(thesaurus,<searchterms>))
    FREETEXT(<FieldNames>,'"<SearchTerm_1>" NEAR "<SearchTerm_2>"')
    FREETEXT(<FieldNames>,'isabout("<SearchTerm_1>" weight(<DecimalWeightValue_1>), <SearchTerm_2> weight(DecimalWeightValue_2>))')
    FREETEXT(<FieldNames>, '<SearchTerm>', LANGUAGE <LanguageCode>)    [for a list of language codes "select [name], alias, lcid from master.sys.syslanguages", use the lcid field]
  6. If you're having problems getting the Thesaurus to appear to work, try using these to look at language settings
    exec sp_configure 'default language'
    SELECT @@language, @@langid
    select [name], alias, lcid from master.sys.syslanguages
  7. You may end up with lots of web pages open, maybe some of them are actually helpful:
    http://www.mssqltips.com/tip.asp?tip=1491
    http://msdn.microsoft.com/en-us/library/ms345187.aspx
    http://msdn.microsoft.com/en-us/library/ms345186.aspx
    http://www.mssqltips.com/tip.asp?tip=1353
    http://www.mssqltips.com/tip.asp?tip=1342
    http://www.mssqltips.com/tip.asp?tip=1332
    http://www.ureader.com/msg/1147186.aspx
    http://msdn.microsoft.com/en-us/library/cc280598.aspx
    http://msdn.microsoft.com/en-us/library/ms176076.aspx
    http://blogs.geekdojo.net/richard/archive/2006/09/01/13805.aspx  (this one is funny as well, which can be nice after not finding anything that seems useful)
    http://www.eggheadcafe.com/community/aspnet/13/10024815/need-help-on-sql-server-2.aspx  (this one seems useful until you realize that there are no replies with answers)
    http://arcanecode.com/2008/05/28/creating-custom-thesaurus-entries-in-sql-server-2005-and-2008-full-text-search/
    http://arcanecode.com/2008/04/29/sql-server-full-text-search-the-fulltextcatalogproperty-function/
    http://www.simple-talk.com/sql/learn-sql-server/sql-server-full-text-search-language-features/
  8. You finally get a sample working, then you realize you need to build a full list of all the Expansions and Substitutions you want.










No comments:

Post a Comment