Preparing SQL Server to store ArcSDE XML columns

Before an ArcSDE XML column can be created in a Microsoft SQL Server database, the database in which it will be stored must be enabled for full-text searching, a full-text catalog must be created, and permission to use the full-text catalog must be granted. ArcSDE XML columns are used by ArcIMS Metadata Services. Therefore, if you will be using metadata services, you must configure your SQL Server instance to store these columns.

When you install SQL Server, be sure to install Full-Text Search. If you already created your database without this, you must add it.

If you upgraded SQL Server and Full-Text Search was not installed in the earlier version of SQL Server, Full-Text Search cannot be installed as part of the upgrade; it must be installed as an additional component after the SQL Server upgrade is completed.

When a database is created using the CREATE DATABASE statement, it is automatically enabled for full-text searching. When a SQL Server 2008 database is created using Management Studio, full-text searching is also enabled by default. However, when a SQL Server 2005 database is created interactively using Management Studio, it is not automatically enabled for full-text searching. Depending on how the database was created and what version of SQL Server you are using, you may have to enable the database for full-text searching.

Note:

If you have a multiple spatial database instance of ArcSDE, a full-text catalog must be added to each spatial database that will contain an XML column. The name of the full-text catalog must be the same in each database.

The user who will own the XML column must have sufficient permissions to create new datasets in the geodatabase. Additionally, REFERENCES permission must be granted to this user on the full-text catalog that will be used to index the contents of the XML column.

Only the database administrator or the owner of the database can enable the database for full-text searching. Once this is accomplished, if a different user performs the remaining steps, he or she must have CREATE FULLTEXT CATALOG permission and must be able to grant the REFERENCES permission to other users.

Steps:

1. In SQL Server Management Studio's Object Explorer, right-click the database server and click New Query.

   A new SQL Query Editor window appears in Management Studio.

2. In the SQL Query Editor window, type and execute the following query to verify the full-text search engine is properly installed:

   SELECT fulltextserviceproperty('IsFulltextInstalled');

   If the value 1 is returned, the full-text search engine has been installed properly. If another value is returned, you must install or reinstall Full-Text Search; you may need to reinstall SQL Server. You cannot successfully create a full-text catalog or an ArcSDE XML column if this query does not return the value 1.

3. Grant permissions to create new datasets in the geodatabase to the user who will own the XML column.
4. Create a full-text catalog. See Creating a full-text catalog in SQL Server.

   If this step is done by anyone other than the database administrator or the owner of the database, CREATE FULLTEXT CATALOG permission and the ability to grant the REFERENCES permission to other users must be granted to the user who will create the full-text catalog.

5. Grant REFERENCES permission on the full-text catalog that will be used to index the contents of the XML column to the user who owns the XML column. (Or grant REFERENCES permission to the role to which the user belongs.)