Full Text Search - thesaurus file is not loaded / working

Question

I am trying to follow the this tutorial for setting up a thesaurus file when querying Full Text Search.

I have removed the comments in the following files

D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\tseng.xml
D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\tsglobal.xml

So now it contains the default synonyms (where for instance jog is replaced with run and vice versa).

Now, I loaded the thesaurus file using

EXEC sys.sp_fulltext_load_thesaurus_file 1033;

and I test my thesaurus using

SELECT * FROM sys.dm_fts_parser ('FORMSOF(THESAURUS,"running")', 1033, 0, 0)

I get only one row with the exact match

(NOTE:

 SELECT * FROM sys.dm_fts_parser ('FORMSOF(Inflectional,"running")', 1033, 0, 0)

returns all "RUN" inflections)

I even try selecting over adventureWorks2012 (after updating a few rows with the word "jog" )

SELECT Description
FROM Production.ProductDescription
WHERE FREETEXT(Description, 'jog')

Nothing is returned. Any Ideas? Thanks.

Answer 1

D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\tseng.xml 

Is the file for British English, and you are specifying the code for US English (1033).

tsenu.xml would be the file name for US English.

If you change the code or the file and reload and it still doesn't work, be sure you are saving the file in Unicode and not UTF-8 or ANSI.

Also, the file path is likely incorrect.

I had a similar issue to the one above, and was led astray by the BOL link. My issue, and possibly yours as well is the path to which the file should be saved:

<SQL_Server_data_files_path>\MSSQL11.MSSQLSERVER\MSSQL\FTDATA\

This is the path for the database you want the thesaurus to be associated's data directory. Though I am running SQL2012, I had created this db in 2008R2 and as a result, it's data dir was in MSSQL10_50MSSQLSERVER\MSSQL\FTDATA

http://sqlblog.com/blogs/greg_low/archive/2008/08/13/modifying-the-thesaurus-in-full-text-search-in-sql-server-2008.aspx