Fulltext search (sql server 2005) works only on some fields..

Question

OK this is the situation..

I am enabling fulltext search on a table but it only works on some fields..

CREATE FULLTEXT CATALOG [defaultcatalog]
CREATE UNIQUE INDEX ui_staticid on static(id)
CREATE FULLTEXT INDEX ON static(title_gr LANGUAGE 19,title_en,description_gr LANGUAGE 19,description_en) KEY INDEX staticid ON [defaultcatalog] WITH CHANGE_TRACKING AUTO

now why the following will bring results

Select * from static where freetext(description_en, N'str')

and this not (while the both have text with str in it ..)

Select * from static where freetext(description_gr, N'str')

(i have tried it also without the language specification - greek in this case) (the collation is of the database is Greek_CI_AS) btw

Select * from static where description_gr like N'%str%'

will work just fine ..

all fields are nvarchar type and the _gr fields hold english and greek text..(should not matter)

All help will be greatly appreciated

Answer 1

Just trying to figure out what's going on: what do you get with this query here?

SELECT * FROM static WHERE FREETEXT(*, N'str')

If you're not explicitly specifying any column to search in - does it give you the expected results?

Another point: I think you have a wrong language ID in your statement. According to SQL Server Books Online:

When specified as a string, language_term corresponds to the alias column value in the syslanguages system table. The string must be enclosed in single quotation marks, as in 'language_term'. When specified as an integer, language_term is the actual LCID that identifies the language.

and from what I found on the internet searching around, the LCID for Greek is 1032 - not 19. Can you try with 1032 instead of 19? Does that make a difference?

Marc