1

I've created a thesaurus file and loaded it as per the example.

I have a database with a table that is full text indexed.

I loaded it using 1033 which is 'English' according to the MSDN article here

It took a long time to load (4 minutes) it's pretty huge, so I know that it loaded it.

There is also a file for British english which is possibly what my SQL is using which would explain why it didn't work. However the number isn't listed on that chart so I do not know how to load it.

Assuming that's the problem, all is dandy. However I also can't find anything that suggests the thesaurus will simply work automatically on any free text query eg. CONTAINS so I don't know if I have to do something to my free text catalog (couldn't see anything, but you never know).

Any ideas?

Additional information:

In my tsenu.xml file:

<XML ID="Microsoft Search Thesaurus">

    <thesaurus xmlns="x-schema:tsSchema.xml">
    <diacritics_sensitive>0</diacritics_sensitive>
        <expansion>
            <sub>John</sub>
            <sub>Jon</sub>
        </expansion>
    </thesaurus>

</XML>

I then ran this:

EXEC sys.sp_fulltext_load_thesaurus_file 1033;
select * from Cats where contains(CatName , 'Jon', language 1033)

There are records for 'Jon' and 'John' but the results are not using the thesaurus to show me alternatives.

NibblyPig
  • 51,118
  • 72
  • 200
  • 356
  • Did you restart the Services ??? It will not take effect until you restart the services from `Sql Server Config Manager` – M.Ali Dec 18 '13 at 20:16
  • I did not, I didn't see anywhere where it said you have to do that. That is something I need to avoid because I want to put this onto my production server and I can't restart that without weeks of notice to clients and headaches. – NibblyPig Dec 18 '13 at 20:34
  • I have rebooted my dev machine but no change. – NibblyPig Dec 18 '13 at 20:37
  • you dont have to take offline your server, Sorry I didnt mention you just need to restart your `Full Text Search Services` not the whole server. I am 101% sure it will not take effect until you restart your FTS services in sql 2005, not to sure about the later editions. but worth giving a go. – M.Ali Dec 18 '13 at 20:43
  • I tried restarting it but it is still not working. – NibblyPig Dec 18 '13 at 20:47
  • try this `WHERE CONTAINS(CatName , 'FORMSOF (THESAURUS, Jon)')` – M.Ali Dec 18 '13 at 20:55
  • Aha, now that worked! Do I have to use FORMSOF every time? I was hoping it would just work with CONTAINS. – NibblyPig Dec 18 '13 at 20:58
  • no you have to use `CONTAINS(CatName , 'FORMSOF (THESAURUS, Jon)')` OR `CONTAINS(CatName , ' (INFLECTIONAL, Jon)')` – M.Ali Dec 18 '13 at 20:59
  • and trust me it does not take effect until you restart the FTS services :) (101% sure about 2005, maybe true for later versions as well) – M.Ali Dec 18 '13 at 21:01
  • That is great thanks. I have only one more question - do you know how I can search 'john smith' using my thesaurus to get 'jon smith' and 'john smith'? I can't figure out how to specify more than 1 word. If you post as an answer I can accept it too :) – NibblyPig Dec 18 '13 at 21:02

2 Answers2

3

try 

WHERE CONTAINS(CatName , 'FORMSOF (THESAURUS, Jon)')

using THESAURUS can be a bit tricky sometime, I havent used FTS for a long time but I remember sometimes , it will only return THESAURUS values not the actual value if it is contained in the data. for example if you are looking for thesaurus values for 'Jon' if there is a 'Jon' is data it will not return 'Jon' but will return all the Thesaurus for Jon, strange but this is how it works.

if you are looking for two words is something like 

WHERE CONTAINS(CatName , '"Word1" AND "Word2"')

To search for John Smith I think you can do something like 

WHERE CONTAINS(NAME, 'FORMSOF (THESAURUS, Jon) OR "Smith"')
M.Ali
  • 67,945
  • 13
  • 101
  • 127
0

Make sure to specify which language you want to use by using the optional language parameter when using Contains or FreeText

Contains and Free Text Information

I also looked at the chart and British English LCID is 2057.

jazakari
  • 66
  • 1
  • 9
  • I ran this query and I *think* mine is set to US, `exec sp_configure 'default language' SELECT @@language, @@langid` but it's hard to be sure because I am struggling to find good documentation. I am doing a simple CONTAINS so I would hope that it'd work, but it's not picking up the alternatives in the thesaurus. – NibblyPig Dec 18 '13 at 20:08
  • Hmm that isn't valid syntax. I tried `select * from MyTable where contains(MyColumn, 'Test', LANGUAGE 1033)` but it did not use the thesaurus. – NibblyPig Dec 18 '13 at 20:32
  • Please provide more information on how you created a thesaurus file and loaded it as per the example. – jazakari Dec 18 '13 at 20:49