Setting the full-text search engine language in SQL Server

If you plan to query a table using the contents of an XML column, you need to make sure the default language that will be used for linguistic analysis with full-text indexes is correct.

This setting is for the SQL Server instance and is independent of a specific database's collation. The default full-text language should be set to match your computer's language and regional settings when SQL Server is installed if that language is supported.

Steps:

1. Connect to the SQL Server instance from SQL Server Management Studio.
2. In Object Explorer, right-click the instance and click Properties.
3. Click Advanced under Select a Page on the Server Properties dialog box.
4. The Default Full-Text Language value is shown in the page on the right under the section Miscellaneous. Leave this dialog box open.
5. Open a SQL Query Editor window and type and execute the following query to list all the full-text languages supported by your version of SQL Server:

   SELECT * FROM sys.fulltext_languages;

   This returns all the full-text languages supported by your SQL Server instance.

6. Compare the Default Full-Text Language value on the Server Properties dialog box to the list of languages and their values in the query window.
   • If the language of the text in your XML documents matches the database server's default full-text language setting, continue preparing the database to store ArcSDE XML data. No changes will have to be made to the DBTUNE full-text language parameter.
   • If the database server's default full-text language setting is neutral, the language of the server did not match one of the languages supported for linguistic analysis. If the language of the text in your XML documents is not one of the languages listed in the default full-text language option topic, you may be able to index and search your documents with some degree of success using the neutral setting if all words in your language are separated by white space and punctuation. If the neutral setting will not work for your language, third-party full-text indexing solutions may be available; after installing and configuring them correctly, you must set the XML_IDX_FULLTEXT_LANGUAGE DBTUNE parameter correctly to index and search your XML documents.
   • If the language of the text in your XML documents does not match the database server's default full-text language setting and the language of your documents is supported for linguistic analysis, you must modify the value of the XML_IDX_FULLTEXT_LANGUAGE DBTUNE parameter to index the text in your XML documents using the appropriate language.
   • If the XML column will contain XML documents written in more than one language, you must use the neutral setting. Stemming will not be performed when indexing the text when the neutral setting is used.

   For more information, see the appropriate topics in SQL Server Books Online.

7. Close the Server Properties dialog box.