Export (0) Print
Expand All

Enabling Others to Issue Full-Text Queries

SQL Server 2000

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

  1. 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.
  2. 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.

  3. 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.

  4. 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.

  5. 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.

  6. 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.

  7. 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.

  8. 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.

  9. 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.

  10. 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.

  11. Check for errors by executing this statement:
    SELECT COLUMNPROPERTY ( OBJECT_ID('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.

  12. 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.

  13. 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.

  14. 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.

  15. 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.

  16. 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.
  17. After repopulation of the PubsCatalog full-text catalog completes, reissue the Transact-SQL query from Step 10. This time, no error occurs.
Show:
© 2014 Microsoft