Enabling Others to Issue Full-Text Queries

Creating and Maintaining Databases

Enabling Others to Issue Full-Text Queries

This example demonstrates how to use SQL Server Service Manager to enable others to issue full-text queries against the writers and books tables. The writers and books tables are fictitious tables located in the pubs database. For a description of the tables, see Administering Full-Text Features Using Stored Procedures and Scalar Functions_administering_full-text_features_using_stored_procedures_and_scalar_functions

Use SQL Server Service Manager to verify the full-text service, Microsoft Search, is running.
If necessary, the service can be started and stopped in one of these ways:
- Use the shortcut menu of the Full-Text Search service in SQL Server Enterprise Manager.
- Use Microsoft Search service in SQL Server Service Manager.
- Type net start mssearch from a command prompt.
Find out if the pubs database has been enabled for full-text processing by executing this statement:
```
SELECT DatabaseProperty ('Pubs',  'IsFulltextEnabled' )
```
For more information, see DATABASEPROPERTY.

This returns 1 if full-text support has been enabled, and 0 if it has not.
If not enabled (which is the default for newly created databases), enable the pubs database for full-text processing. Using pubs, execute this stored procedure:
```
sp_fulltext_database  'enable'
```
For more information, see sp_fulltext_database.
Create a full-text catalog named PubsCatalog, opting for the default directory, by executing this stored procedure in the pubs database:
```
sp_fulltext_catalog 'PubsCatalog', 'create'    
```
For more information, see sp_fulltext_catalog.

This creates meta data about a full-text catalog in the system tables of the database and builds an empty full-text catalog in the file system.
Register the writers and books tables for full-text processing by executing this stored procedure once for each table:
```
sp_fulltext_table  'writers',  'create',  'PubsCatalog', 
'writer_id_index'
sp_fulltext_table  'books',  'create',  'PubsCatalog', 'isbn_index'
```
For more information, see sp_fulltext_table.

Both tables use the PubsCatalog full-text catalog. These stored procedure calls create meta data about both full-text indexes.
For each table, specify the names of the columns that are to support full-text queries by executing this stored procedure once for each column:
```
sp_fulltext_column  'writers',  'organization',  'add' 
sp_fulltext_column  'writers',  'bio',  'add' 
sp_fulltext_column  'books',  'writer_name',  'add'   
sp_fulltext_column  'books',  'the words',  'add' 
sp_fulltext_column  'books',  'abstract',  'add' 
```
For more information, see sp_fulltext_column.

Note A mistake was made for the sake of illustration: for the books table, the writer_name column, rather than the titles column, has been registered.

These stored procedure calls augment meta data about both full-text indexes.
Create a full-text index for these tables by executing this stored procedure once for each table:
```
sp_fulltext_table  'writers',  'activate'
sp_fulltext_table  'books',  'activate'
```
This does not actually create the full-text indexes. Rather, it registers the tables in the full-text catalog so that data from these tables will be included in the next population.
Start a full population of the PubsCatalog full-text catalog by executing this stored procedure:
```
sp_fulltext_catalog  'PubsCatalog',  'start_full'
```
Because the population of a full-text catalog is an asynchronous operation, it is unlikely that the full-text indexes are created immediately.
Verify the progress of the population of the PubsCatalog full-text catalog by executing this statement:
```
SELECT FulltextCatalogProperty ( 'PubsCatalog',  'PopulateStatus')
```
For more information, see FULLTEXTCATALOGPROPERTY.

This returns 0 if the service is idle for the full-text catalog and therefore finished, and 1 or more to indicate the stage of population.

Issue Transact-SQL queries to confirm that the administration was executed correctly. For example:

SELECT B.writer_name, B.pub_date, B.the_words, A.royalties_ytd
FROM writers A,  books B
WHERE A.writer_name = B.writer_name
AND A.citizenship = 'Canadian'
AND CONTAINS (B.the_words, '"Indexing Service" NEAR "Indexing Service"')

SELECT writer_name, pub_date, abstract
FROM books  
WHERE CONTAINS (title, '"Classic" NEAR "French" NEAR "Cooking"')

This last query results in an error because the title column was not enabled for full-text queries.

Check for errors by executing this statement:
```
SELECT ColumnProperty ( ObjectId('books'), 'title',  'IsFullTextIndexed' )
```
For more information, see COLUMNPROPERTY.

This returns 1 if the title column is part of the full-text index for the books table, and 0 if it is not.
List the columns participating in full-text processing for the books table by executing this stored procedure:
```
sp_help_fulltext_columns  'books'
```
For more information, see sp_help_fulltext_columns.

Note The results of this query show there was a mistake and that the writer_name column, rather than the title column, was included in the full-text index definition.
Deactivate the books table so that the title column can be added to the full-text index and the writer_name column can be removed by executing this stored procedure:
```
sp_fulltext_table  'books',  'deactivate'
```
In addition to allowing columns to be added and deleted, deactivating the books table means the table no longer participates in the population of the PubsCatalog full-text catalog. However, the meta data remains and the table can be reactivated. The existing full-text index for the books table remains in place until the next full population of the PubsCatalog full-text catalog, but it is unused because Microsoft® SQL Server™ 2000 blocks queries on deactivated tables.
Add the title column and remove the writer_name column from the meta data for the full-text index of the books table. Execute this stored procedure once for each column:
```
sp_fulltext_column  'books',  'writer_name',  'drop'   
sp_fulltext_column  'books',  'title',  'add'
```
For more information, see sp_fulltext_column.
Reactivate the books table using this stored procedure:
```
sp_fulltext_table  'books',  'activate'
```
If the table is reactivated and the index is not repopulated, the old index is still available for queries against the remaining full-text enabled columns, but not for queries against any new full-text enabled columns. Before repopulation, data from deleted columns can be matched on queries that specify a search of all full-text columns by typing an asterisk (*) for the column name.
Start an incremental population of the PubsCatalog full-text catalog by executing this stored procedure:
```
sp_fulltext_catalog  'PubsCatalog',  'start_incremental'
```
An incremental population refreshes the full-text catalog by indexing data in full-text enabled columns with these characteristics:
- Rows that have been updated or inserted since the last population.
- Tables that have a timestamp column.
- All rows that have been enabled for full-text processing since the last population, or that have a schema that has been modified in any way since the last population.
After repopulation of the PubsCatalog full-text catalog completes, reissue the Transact-SQL query from Step 10. This time, no error occurs.

previous page start next page