Stored Procedures Scenario

Following is an outline of the steps that an administrator operating in the “stored procedure mode” could take to set up full-text search on selected tables and columns in the pubs database. This scenario does not demonstrate everything that is available, but only enough to provide insight as to how these procedures can be used.

1. Check that Microsoft Search Service is running by looking at the shape or color of the icon for full-text search in SQL Server Enterprise Manager.

   If necessary, the service can be started in one of several ways:

   • Through the context menu of the full-text search object in SQL Server Enterprise Manager
   • Outside SQL Server through the Service Control Manager (in the Service Control Manager, this service is called Microsoft Search Service)
   • From a MS-DOS prompt by typing net start mssearch
   • From SQL Server service manager

   The full-text service also can be stopped in all of these places.

   In this case, pubs has not been enabled.

2. Determine if the pubs database has been enabled for full-text processing. The SQL statement SELECT DatabaseProperty('pubs', 'IsFulltextEnabled') returns 1 if the service has been enabled, and 0 if it has not. In this case, the service has been enabled.
3. Connect to the pubs database by executing the Transact-SQL statement:

   USE pubs

4. Enable pubs for full-text search by invoking the stored procedure:

   sp_fulltext_database 'enable'

5. Create a full-text catalog named PubsCatalog, and choosing the default directory. This is done by invoking the stored procedure:

   sp_fulltext_catalog 'PubsCatalog', 'create'

   This procedure creates metadata about a full-text catalog in the system table of the database and builds an empty full-text catalog in the file system. The file is created in a default root directory that can be overridden, if desired, by using a third parameter.

6. Register the authors, jobs, pub_info, and titles tables for full-text processing. Tables so registered must have a column (called the full-text unique key column) that is guaranteed to have a unique value for each row. Because all of these tables have a primary key that consists of a single column, they all qualify.

   To register such a table, you must specify the name of the index that enforces the unique value for the unique key column. For a given table, this information can be obtained using the sp_helpindex stored procedure. The indexes of interest in this scenario are:

    

   authors	UPKCL_auidind
   jobs	PK_jobs_22AA996
   pub_info	UPKCL_pubinfo
   titles	UPKCL_titleind

   
   

   Knowing these names, it is now possible to register the tables by invoking the sp_fulltext_table stored procedure once for each table:

   sp_fulltext_table 'authors', 'create', 'PubsCatalog', 'UPKCL_auidind'

   sp_fulltext_table 'jobs', 'create', 'PubsCatalog', 'PK_jobs_22AA996'

   sp_fulltext_table 'pub_info', 'create', 'PubsCatalog', 'UPKCL_pubinfo'

   sp_fulltext_table 'titles', 'create', 'PubsCatalog', 'UPKCL_titleind'

   The effect of these invocations is to update the metadata in the system tables both for this full-text catalog and for these tables.

7. For each of the newly registered tables, specify the names of the columns that are to be registered. This is done by invoking the sp_fulltext_column stored procedure once for each column:

   sp_fulltext_column 'authors', 'address', 'add'    

   sp_fulltext_column 'jobs', 'job_desc', 'add'  

   sp_fulltext_column 'pub_info', 'pr_info', 'add'  

   sp_fulltext_column 'titles', 'type', 'add'

   sp_fulltext_column 'titles', 'notes', 'add'      

   The effect of these invocations is to augment the metadata in the system tables.

Note A mistake was made for the sake of later illustration. For the titles table, the type column, rather than the titles column has been registered.

8. Before a full-text index can be created, it must be active. Activate the ability to create a full-text index for these tables by invoking the sp_fulltext_table stored procedure once for each table:

   sp_fulltext_table 'authors', 'activate'    

   sp_fulltext_table 'jobs', 'activate'

   sp_fulltext_table 'pub_info', 'activate'    

   sp_fulltext_table 'titles', 'activate'

   This does not create the full-text indexes; it only registers the tables as active in the metadata of the full-text catalog so that data from these tables will be included in the next population. Additionally, this defines full-text population start seeds for each table to the full-text service.

9. Start a full population of the PubsCatalog full-text catalog by invoking the sp_fulltext_catalog stored procedure:

   sp_fulltext_catalog 'PubsCatalog', 'start_full'    

   The population of a full-text catalog is an asynchronous operation. This means that immediately following the execution the procedure and the return to the procedure’s caller, it is unlikely that the full-text indexes will have been created yet.

10. Inquire into the progress of the population of the PubsCatalog full-text catalog. This statement SELECT FulltextCatalogProperty ('PubsCatalog', 'PopulateStatus') returns 0 if the service is idle for the full-text catalog and therefore (supposedly) finished, and 1 or more to indicate various stages of population.
11. Issue some SQL queries to confirm that the administration was executed properly. For example, issue the following SQL statement:

    SELECT P.pub_name, T.title, T.price

      FROM publishers P, titles.T

      WHERE P.pub_id = T.pub_id

        AND P.country = 'England'

        AND CONTAINS (T.notes,

                      '"case is altered" OR

                       "cat and custard pot" OR

                       "the monarch and the sphinx"

                      ' )

12. Issue the following SQL query:

    SELECT title_id, title, pubdate

      FROM titles

      WHERE CONTAINS (T.title,

                      'classic ~ french ~ cooking')

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

13. Check for mistakes by using this statement, which returns 1 if title is part of the full-text index for the books table, and 0 if it is not.

    SELECT ColumnProperty ( ObjectId('titles'),

                            'titles',

                            'IsFulltextIndexed' )

    In this case, the value returned is 0.

14. List the columns that participate in full-text processing for titles by invoking the stored procedure:

    sp_help_fulltext_columns 'titles'

    The results of this query will show that there was a mistake and that type instead of title was included in the full-text index definition.

15. Deactivate the titles table so that the title column can be added to the full-text index and the type column can be removed. This is done by invoking the stored procedure:

    sp_fulltext_table 'titles', 'deactivate'

    In addition to allowing columns to be added and deleted, the effect of deactivating the titles table means that the table no longer participates in the population of the PubsCatalog full-text catalog. However, the metadata remains and the table can be reactivated. The existing full-text index for the titles table remains in place until the next full population of PubsCatalog, but it is unused because SQL Server blocks queries on deactivated tables.

16. Add the title column and remove the type column from the metadata for the title table’s full-text index. This is done by invoking the sp_fulltext_column stored procedure once for each column:

    sp_fulltext_column 'titles', 'type', 'drop'

    sp_fulltext_column 'titles', 'title', 'add'

17. Reactivate the books table by invoking the stored procedure:

    sp_fulltext_table 'titles', 'activate'

    If the table is reactivated and the index is not repopulated, the old index is still available for queries against all the full-text registered columns that remain (but not against any new full-text registered columns). Data from deleted columns will be matched on queries that specify a * search ( that is, all full-text columns in a table).

18. Start an incremental population of the PubsCatalog full-text catalog by invoking the stored procedure:

    sp_fulltext_catalog 'PubsCatalog',

                        'start_incremental'

    An incremental population will refresh the full-text catalog by indexing data in the full-text enabled columns that meet any of the following criteria:

    • Data from rows that have been updated or inserted since the last population in tables that have a timestamp column
    • Data from all rows in tables that do not have a timestamp column, tables that were enabled for full-text processing since the last population, or tables whose schemas have been modified in any way since the last population
19. After the repopulation of PubsCatalog is complete, reissue the query from step 13. This time, no error is raised.