Recently I have experienced a number of problems with my Full-Text indexes in SQL Server (2005). Creating or updating the columns being indexed, I had this error:
Default full-text index language is not a language supported by full-text search.
I run SELECT * FROM sys.fulltext_languages; and I got no results, when I should have had in my install at least 17.
The fist time it happened, I couldn’t find any simple solution. They all pointed me to reinstalling SQL Server, but I hate reinstalling SQL Server, it takes too long (And luckily it isn’t Oracle). I refused to reinstall all the services so what I did was:
- Uninstall only the full text service.
- Reinstall the service again
- Apply the service pack again.
- Re-start all SQL Server services.
That solved the problem but it happened again.
After searching for solutions, I couldn’t find any reasonable solution and what’s more important, why it did happen. I thought that it would be something related to our DB update scripts, but it wasn’t.
Investigating, I noticed that SQL Server creates some entries in the registry with the word separators and other information related to the ways the data gets indexed depending on the languages it has to index.
Then, BINGO. I remembered that I had installed an app to help cleaning the registry, IObit – Advanced SystemCare 3 (A free version of the tool that supposedly optimised the system but what it did was deleting all the registry keys that defined the Full-Text languages, and who knows what else). I went to the Restore Centre from that tool and restored all the changes made to bring back all the deleted registry entries.
I run exec sp_fulltext_service ‘update_languages’; to update the languages and my languages were bac.
Now, SELECT * FROM sys.fulltext_languages; returned all the languages.
If you have this error and you have any tool to clean the registry, have a look at it to see what it has messed up. I better have a dirty registry rather that some applications that don’t work and I spend a lot of time trying to figure out what happened.