Index Server Faq
Using the SQL FTS Stored Procedures to index your table
This stored procedure is used to manipulate/query the Full Text Service, MSSearch Service. Sp_FullText_Service has two arguments: action and value.
The Action argument???s Possible Values
Resource Usage specifies the amount of CPU resources that are dedicated to Full Text Search. Acceptable values are between 1 and 5, where 1 is background and 5 is dedicated; the default is 3. Changing this value sets the registry key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\ Gathering Manager\ PerformanceLevel. Changes become active when you next restart MSSearch Service The default of 3 is optimized for up to 1,000,000 rows, and you will not get much noticeable performance improvement until you start dealing with tables of this magnitude.
Syntax: Sp_FullText_Service ???Resource_Usage???, 5
You can check the current settings for your Full Text Service by running the following command: Select FULLTEXTSERVICEPROPERTY (???resourceusage???)
Clean Up Removes catalogs in the file system that do not have corresponding entries in the sysfulltextcatalogs table. The sysfulltextcatalogs are created in each database that is Full Text enabled. If the MSSearch service is not running when you drop/delete a Full Text catalog or database, these entries can get out of sync. Databases can be dropped from ISQLW, Enterprise Manager; and Full Text catalogs can be dropped from ISQLW, Enterprise Manager, and through the Search Admin MMC.
Syntax: Sp_FullText_Service ???Clean_Up???
Connect Timeout Allows you to change the connection timeout for Full Text Service when your MSSearch is connecting to SQLServer to start the indexing process. If your SQL Server is extremely busy doing some processor intensive operation, you may get this timeout. You will get a message in your Gatherer logs indicating this. This parameter is in seconds, with the default being 300 seconds (5 minutes). The maximum value is 9 hours, 6 minutes, 7 seconds. Running this stored procedure will change the value of the registry key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\Gathering Manager\Applications\SQLServer\ConnectTimeout. Note that running this stored procedure will affect all of your databases. An indication of the need to increase this value would be getting the following message in the Gatherer log.
7/29/2001 12:07:04 PM Add Started Full crawl
7/29/2001 12:08:20 PM MSSQL75://SQLServer/6fe99f9f Add Error fetching URL,
(800705b4 ‑ This operation returned because the timeout period expired.)
You would get this somewhat ambiguous message in the Application log of your event viewer:
One or more warnings or errors for Gatherer project <SQLServer SQL0000500005>
were logged to file <C:\Program Files\Microsoft SQL Server\MSSQL\FTData\SQLServer\GatherLogs\SQL0000500005.3.gthr.
If you are interested in these messages, please, look at the file using the Gatherer log query object (gthrlog.vbs, log viewer Web page).
Sp_fullTextServer ???connect_timeout???, 300
You can check the current settings for your Full‑Text Service by running the following command:
Select FULLTEXTSERVICEPROPERTY (???connecttimeout???)
Data Timeout‑Allows you to change the data timeout for Full‑Text Service when MSSearch is retrieving data from SQLServer. MSSearch retrieves data on a row‑by‑row basis, and you may want to increase this value if you are indexing very large text or image columns, or when you have frequent table/page or row locking that might cause this timeout. This parameter is in seconds, with the default being 120 seconds (2 minutes). The maximum value is 9 hours, 6 minutes, 7 seconds. Running this stored procedure will change the value of the registry value HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\Gathering Manager\Applications\SQLServer\DataTimeout. Note that running this stored procedure will affect all of your databases.
You might want to increase your resource usage if you get these errors. Also note that if you have your resource usage set to 1 or background, whatever you put for datatimeout is ignored.
7/29/2001 12:56:02 PM MSSQL75://SQLServer/75d7831f Add Error fetching URL,
(80040d7b‑Document filtering could not be completed because the document server
did not respond within the specified timeout. Try crawling the server later, or
increase the timeout values. )
You would get this somewhat ambiguous message in the Application log of your event viewer:
The crawl seed <MSSQL75://SQLServer/75d7831f> in project <SQLServer SQL0000500005> cannot be accessed. Error: 80040d7b‑Document filtering could not be completed because the document server did not respond within the specified timeout. Try crawling the server later, or increase the timeout values.
Sp_fullTextServer ???datatimeout???, 300
You can check the current settings for MSSearch service by running the following command:
Select FULLTEXTSERVICEPROPERTY (???datatimeout???)
Syntax: Sp_help_fulltext_catalogs ???catalog_name???
Sp_help_fulltext_catalogs @fulltext_catalog_name = ???catalog_name???
(This final unqualified stored procedure will return information about all Full Text catalogs in your database.)
Running this stored procedure will return a Results Set listing:
Full Text catalog ID (ftcatid),
Full Text catalog name,
Full Text catalog path (a value of null indicates the default location of the catalogs, which is C:\Program Files\Microsoft SQL Server\MSSQL\FTData),
Status (the same status codes are used as in the PopulateStatus column from the FullTextCatalogProperty metafunction above),
and Number of tables in the catalog.
Note that the number of tables is the number of tables that are Full Text Indexed. It does not matter whether they are activated or deactivated. The procedure sp_help_FullText_catalogs_Cursor will return the same results set, but only as a cursor. A cursor is an in memory resident results set that you can populate with data and access it row by row navigating the rows in any direction in a sequential fashion.
Syntax: sp_help_fulltext_catalogs @cursor_variable OUTPUT, 'catalog_name'
sp_help_fulltext_catalogs @cursor_return = @cursor_variable OUTPUT, @fulltext_catalog_name = 'catalog_name'
sp_help_fulltext_catalogs @cursor_return OUTPUT
(This last stored procedure will report on all catalogs in the database that you are running this stored procedure from.)
DECLARE @Cursor_Name CURSOR
EXEC sp_help_fulltext_catalogs_cursor @Cursor_Name OUTPUT, ???Catalog_Name'
FETCH NEXT FROM @Cursor_Name WHILE (@@FETCH_STATUS > 0)
FETCH NEXT FROM @Cursor_Name
The same results set is returned as above.
The sp_fulltext_database has a single parameter with two possible values: enable and disable.
This enables the database for Full Text Indexing. If you run this stored procedure with the enable parameter on a Full Text database that already has Full Text catalogs in the database, it will cause the catalogs to be rebuilt. You will need to do a full population on this catalog in order to be able query it again.
This disables the database for Full Text Indexing. This parameter will remove all of the Full Text catalogs in the file system and will disable the database for Full Text Indexing. The Full Text metadata on the tables and catalogs will remain intact. The Full Text metadata is contained in the two system tables sysfulltextcatalogs and sysobjects (in the status (bitwise or???d with 0x189, and ftcatid columns).
Syntax: Sp_fulltext_Database ???pubs???, ???enable???
You can query the Full Text properties of your database by using the metadata function FULLTEXTDATABASE.
Syntax: Select DATABASEPROPERTY(???database_name???,???IsFulltextEnabled???)
A value of 1 would indicate that this database is Full Text enabled; a value of 0 would indicate that it is not. You can also use
which is an extended version of DATABASEPROPERTY, but for Full Text Search, it does not add anything more than what appears in DATABASEPROPERTY.
The stored procedure sp_fulltext_table is used to enable or disable a table for Full Text Indexing and to set the type of population on your table. When you Full Text Index a table it will change the status (bitwise, and with hex 189, and add an entry to the ftcatid column of sysobjects). Disabling it will nor hex 189 in the status column and remove the entry from the ftcatid; colstat is changed to 16 in syscolumns. Arguments are tablename, action, catalog name, and keyname.
Table names can be one or two part (i.e. dbo.table_name or db_user_name.table_name). Acceptable values for the argument action:
Create The create option is used to specify that a table is to be Full Text Indexed. It will set up the metadata in the system tables and specify which catalog will contain the index for this table. You must define the key or the index that will be used by Full Text Indexing and Full Text Search to uniquely identify your row. This index or key must exist before running this command; otherwise, you will get an error message. You cannot query your Full Text Index until it has been populated.
Syntax: Sp_fullText_Table ???table_name???, ???Create???, ???catalog_name???, ???PK???
where table_name is the table you are Full Text Indexing, ???Create??? is the action, ???catalog_name??? is the name of the catalog, and ???PK??? is the primary key that provides a unique identifier for Full Text Indexing and Full Text Searching. You can check to see what catalog your table belongs to by issuing the following command: Select OBJECTPROPERTY(object_id(???table_name???),???TableFulltextCatalogId???) This will return the catalog ID that the table is indexed by. A 0 indicates that this table is not Full Text Indexed.
You will have to issue the following command to get the catalog???s name:
Select name from sysfulltextcatalogs where ftcatid= OBJECTPROPERTY(object_id(???table_name???),??? TableFulltextCatalogId???)
You can check to see which key is the Full Text Key by issuing the following command:
A 0 will indicate that this table is not being Full Text Indexed. A Null will indicate you have made a typo in the table name. This query will return the column ID. If you need to find the column name, try this query:
Select name from syscolumns where colid=OBJECTPROPERTY(object_id('table_name'),'TableFullTextKEYCOLUMN') and id=object_id('table_name')
Drop This option is used to remove Full Text Indexing from a table. It will remove the Full Text metadata from the system tables. If the table is activated, it will be automatically deactivated. What this means is that if you reactivate the table (sp_fulltext_table ???table_name???, ???create???, ???catalog_name???, ???PK???), you will not be able to query the Full Text Index again without having to do a population.
Syntax: Sp_fullText_Table ???table_name???, ???Drop???
Activate This activates the table for Full Text Indexing. A table must have at least one column being indexed before a table can be activated. You do this through sp_fulltext_column. A table is made active as soon as the first column is added for indexing. If the last column is dropped from the index, the table is made inactive. If Change Tracking is on, activating an inactive index starts a new (full) population. Activation by itself does not cause a population to occur; it merely creates the metadata in the system tables, indicating that the table is to be Full Text Indexed and is queryable. If you have a table that has been Full Text Indexed (e.g. a population has occurred) and then you deactivate it, the index will still exist in the catalog, but you will be unable to query the table. If you then re activate the table, you will be able to re run your queries without having to do a new population, but any inserts/updates in the table while that table was deactivated will not be reflected in these searches.
Syntax: Sp_fullText_Table ???table_name???, ???Activate???
Deactivate Makes changes to the metadata/system tables indicating that a table is not Full Text Indexed and is not Full Text Searchable or queryable. The index will still exist, and you will be able to run your queries once you reactivate your table, but the data may be old. If Change Tracking is on, and you then deactivate your table, the data will remain in sysfulltextnotify until you reactivate your table. If you have Change Tracking on and then you deactivate a table, all changes that are currently in sysfulltextnotify will be processed, but changes after that will not be and, consequently, will never get into the index. You will have to run a full population in order to get a complete index.
Syntax: Sp_fullText_Table ???table_name???, ???Deactivate???
Start_change_tracking Starts the change tracking process. As soon as you issue this command, a full population will occur. Enabling change tracking is not enough; you must either update the index at discrete intervals or you must set ???update index in background??? (sp_fulltext_table 'table_name', 'start_background_updateindex') so that any changes to your table are indexed. You do not need to have a timestamp column on your table for change tracking to work. Columns that are being Full Text Indexed in a table will have the tableid, rowinfo and the ftkeyid (in VarBinary) written to the sysfulltextnotify table. TableID can be determined from select ID from sysobjects, where name=???tablename???, rowid is an internal identifier (normally 1), and ftkeyid is a sequential hex value that MSSearch uses internally to map rows to keys in the source tables. Full text change tracking does not track any WRITETEXT or UPDATETEXT operations performed on Full Text Indexed columns that are of type image, text, or ntext. To enable this, you will have to update your text/image column immediately after doing the WRITETEXT or UPDATETEXT operation, setting the text/image fields equal to themselves.
Syntax: Sp_fullText_Table ???table_name???, ???Start_change_tracking???
You can check to see if this table has change Tracking activated by issuing the following command:
Select OBJECTPROPERTY(object_id(???table_name???),???TableFullTextChange TrackingOn???)
A 1 will indicate that this table has change tracking activated on it, and a 0 will indicate that it does not, or that the table is not being Full Text Indexed.
Stop_change_tracking Stops change tracking.
Syntax: Sp_fullText_Table ???table_name???, ???Stop_change_tracking???
Update_index All changes in the sysfulltextnotify table will be indexed.
Syntax: Sp_fullText_Table ???table_name???, ???Update_index???
Start_background_updateindex Starts background indexing of the table. Rows that are modified will have their key written to the sysfulltextnotify table, and when the system idle condition is reached from more than 100 ms, these rows will be indexed. As soon as you enable this, a full population of your table will occur. You do not need to have a timestamp column on your table for change tracking to work.
Syntax: Sp_fullText_Table ???table_name???, ???Start_background_updateindex???
Stop_background_updateindex Stops the background indexing of the table. Rows in the sysfulltextnotify table that have not been past MSSearch will not be indexed until you re enable Change tracking in the background again. New modifications to your indexed table will not be written to this the sysfulltextnotify table.
Syntax: Sp_fullText_Table ???table_name???, ???Stop_background_updateindex???
You can check to see if your table has background indexing enabled by issuing the following command:
Select OBJECTPROPERTY(object_id(???table_name???), ???TableFullTextBackgroundUpdateIndexOn???)
This will return 1 if background indexing is enabled and 0 if it is not.
Start_full ??? Starts a full population. The entire table will be extracted out row by row for the Full Text Indexing process.
Syntax: Sp_fullText_Table ???table_name???, ???Start_Full???
Start_incremental ??? Starts an incremental population on a table. Only rows that have been updated or inserted will be indexed. If there is no time stamp column on this table, a full population will be done. Rows that have columns other than the Full Text Indexed columns updated will also be indexed.
Syntax: Sp_fullText_Table ???table_name???, ???Start_incremental???
Stop ??? Stops a full or incremental population on a table.
Syntax: Sp_fullText_Table ???table_name???, ???Stop???
Sp_help_FullText_tables and sp_help_FullText_tables_Cursor
You can also use the two stored procedures sp_help_fulltext_tables and sp_help_FullText_tables_Cursor to report on your Full Text Tables.
Sp_help_FullText_tables has two arguments: the catalog name and the table name (which is optional).
Syntax: Sp_help_FullText_tables ???Catalog_name???,???Table_name???
Sp_help_FullText_tables @fulltext_catalog_name = 'catalog_name', @table_name = 'table_name'
or just simply
Sp_help_FullText_tables @fulltext_catalog_name = 'catalog_name'
(This will return information on all the tables in this catalog.)
The results set that is returned by running this stored procedure will list the following:
Table_owner (whoever created the table, normally aliased as dbo if the logon ID is a dbo)
Full Text key name (normally the table???s primary key name)
Full Text key ID (the table???s primary key col_id, found by issuing the query name) Whether the table is active or not (1 indicates that the table is active, 0 inactive)
Full Text Catalog Name
Sp_help_fulltext_tables allows you to navigate through the results set of the above stored procedure. The results set is identical to the results set from sp_help_fulltext_tables.
Syntax: sp_help_fulltext_tables_cursor @cursor_return = @my_cursor OUTPUT, @fulltext_catalog_name = 'catalog_name' , @table_name = 'table_name'
sp_help_fulltext_tables_cursor @my_cursor OUTPUT, 'catalog_name' , 'table_name'
sp_help_fulltext_tables_cursor @my_cursor OUTPUT, 'catalog_name'
(This will return a result set listing all tables in the catalog.)
The results set contains all of the above columns:
Full Text key name
Full Text key ID, whether the table is active or not
Full Text Catalog Name
Typical usage of this stored procedure:
DECLARE @mycursor CURSOR
EXEC sp_help_fulltext_tables_cursor @mycursor OUTPUT, 'catalog_name',???table_name???
FETCH NEXT FROM @mycursor WHILE (@@FETCH_STATUS > 0)
FETCH NEXT FROM @mycursor
Sp_FullText_Column is the stored procedure that adds or drops columns of your table to your Full Text Index. When you use this stored procedure, any population in progress will be stopped. Arguments are
Values for the action argument are add and drop.
Add Add is used to add a column to Full Text Indexing. You must have enabled the table for Full Text Indexing before issuing this command, and you must have specified a Full Text key. The column you are adding must be char, varchar, nchar, nvarchar, text, ntext, or an image datatype.
Syntax: Sp_FullText_Column ???table_name???, ???column_name???,???add???
Drop Drop is used to drop a column for your Full Text table.
Language You can also specify the language for your column so that the correct word breaker will be applied to this column. You have two choices to apply the language: hex values and numeric values. If you do not specify a value, the system locale (sp_configure ???default full text language???) will be applied. If you use a language for which there is no word breaker, the system locale will again be applied. Here is a list of the supported languages. The string value is provided for informational purposes only; it cannot be used in sp_fulltext_columns.
Languages and locales in SQL FTS Language
If you have multiple languages or a language or charset not specified above in your column, use the Neutral language.
Syntax: Sp_FullText_column ???table_name???,???column_name, 1053
In this case we are telling Full Text Search to use the Swedish word breaker when running your query and indexing your rows. The word breaker is not only used when you index your rows, but also when you issue your query. So if you do a FREETEXTTABLE query, a CONTAINS query using formsof(Inflectional, SearchPhrase) or the wild card operators, or are using a language that does not have white space delimiters for word boundaries, MSSearch applies the word breaker for the language of your column or the default locale for your SQL Server (sp_configure 'default full Text language') to your search phrase and then searches your Full Text Index for matches.
Equally valid is this syntax:
This will use the default word breaker as specified in sp_configure 'default full Text language'.
or Sp_FullText_column ???table_name???,???column_name???, 0x041d
This is the hex value for the Swedish language.
Type_Column_Name If you are using the image datatype, you can store documents of different types in these columns and have MSSearch apply the IFIlter to the documents stored in that column. So you could store PDFs, Word documents, Excel spreadsheets, email messages from Outlook, Outlook Express Newsgroup postings, RTFs, AutoCad drawings, etc. all in the same table and the same column. For example, one row???s image column could contain a PDF, the next row a Word document, a third row an AutoCad drawing, and a fourth row a text file???in short, anything an IFilter is written for. However, you must tell MSSearch what type of document is in the image column for that row. You communicate to MSSearch what your document type for each row is by pointing toward a column that contains the extension for that document. The length of this column should be char(3). Here is a sample table illustrating this: The below table illustrates the document type column.
PK IMAGE_COL (abridged) type_colname Document type
1 0x46726F6D3A20224D696B6520 Nws Outlook Express news post
2 0x0D0A0D0A48696C6172792 Txt Text file
3 4469636B2053616C69676 Htm HTML page
You can check which IFilter MSSearch will apply for a particular extension by running filtreg. In Win2k you can associate different IFilters with different document types from the command line like this: filtreg .txt .log Here we are associating the extension log to use the same IFilter as txt.
Syntax: Sp_FullText_column ???table_name???,???column_name???, 0x041d, ???column_type_name???
If you do not specify an extension in the type_colname argument, the text IFilter (query.dll) will be applied. The text IFilter has an algorithm built into it that will attempt to do word breaking on the IFilter, but will give up if it can???t discern words. Running sp_FullText_column on any table that is activated for Full Text Indexing will cause any populations and Change tracking to stop. You can also use the two stored procedures sp_help_fulltext_columns and sp_help_fulltext_columns_Cursor to report on your Full Text tables. These stored procedures will return a results set containing the following columns:
Table owner (the name of the table owner or creator, normally aliased as dbo)
TableId (select ID from sysobjects where name=???table_name???)
Table name Name of the Full Text column
Full Text col id Full Text column
Blob type column name (the type_column_name, the name of the column that tells MSSearch what type of document is stored in the Image datatype; this will be null if the column is not the image datatype)
Full Text column blob type col id
Full Text language for that column
There will be one row for each column of the table that is being Full Text Indexed.
Syntax: sp_help_fulltext_columns @table_name = 'table_name' , @column_name = 'column_name'
sp_help_fulltext_columns 'table_name' , 'column_name'
This will return information on all of the columns in the database that are being Full Text Indexed. sp_help_fulltext_columns_cursor allows you to navigate through the results set of the above stored procedure. The results set is identical to the results set from sp_help_fulltext_columns.
Syntax: sp_help_fulltext_columns_cursor @cursor_return=@my_cursor OUTPUT, @fulltext_catalog_name= 'catalog_name' , @table_name = 'table_name'
sp_help_fulltext_columns_cursor @my_cursor OUTPUT, 'table_name' , ???column_name???
sp_help_fulltext_columns_cursor @my_cursor OUTPUT, 'table_name'
This will return a result set listing all columns in the table.
Typical usage of this stored procedure:
DECLARE @mycursor CURSOR
EXEC sp_help_fulltext_columns_cursor @mycursor OUTPUT, 'table_name???,???column_name???
FETCH NEXT FROM @mycursor
WHILE (@@FETCH_STATUS >0)
FETCH NEXT FROM @mycursor
designed by :: smilla group