Hi,

Full-text catalogs and indexes are not stored in the database to which they
belong. The catalogs and indexes are managed separately by the Microsoft
Search service. So Install MS search and follow these steps -

/*
1. Create and populate a table.
2. Enable the current database for full-text searching.
3. Create a full-text catalog.
4. Register the new table and certain columns in it for full-text
search.
5. Populate the new full-text catalog with full-text index information
from the new table.
6. Execute a full-text query against the new table
*/

-- Create and populate a table.
IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE ID =
OBJECT_ID(N'DBO.FTSTABLE') AND OBJECTPROPERTY(ID, N'ISUSERTABLE') = 1)
DROP TABLE DBO.FTSTABLE
GO

CREATE TABLE DBO.FTSTABLE (
ID INT IDENTITY (0, 1) NOT NULL ,
TEXT TEXT NULL )
GO

CREATE UNIQUE CLUSTERED INDEX IX_FTSTABLE ON DBO.FTSTABLE(ID) WITH
FILLFACTOR = 80
GO

INSERT INTO FTSTABLE VALUES('ABC DEF GHI')
INSERT INTO FTSTABLE VALUES('JKL MNO PQR')
INSERT INTO FTSTABLE VALUES('STU VWX YZ')
GO
----------***----------

-- Enable the current database for full-text searching.
IF (SELECT DATABASEPROPERTY(DB_NAME(), N'ISFULLTEXTENABLED')) <> 1
EXEC SP_FULLTEXT_DATABASE N'ENABLE'
GO
----------***----------

IF (SELECT INDEXPROPERTY(OBJECT_ID('FTSTABLE'), 'IX_FTSTABLE',
'ISFULLTEXTKEY')) = 1
EXEC SP_FULLTEXT_TABLE 'FTSTABLE', 'DROP'
GO
----------***----------

-- Create a full-text catalog.
IF EXISTS (SELECT * FROM DBO.SYSFULLTEXTCATALOGS WHERE NAME =
N'FTS-CATALOGS')
EXEC SP_FULLTEXT_CATALOG N'FTS-CATALOGS', N'DROP'
GO

IF NOT EXISTS (SELECT * FROM DBO.SYSFULLTEXTCATALOGS WHERE NAME =
N'FTS-CATALOGS')
EXEC SP_FULLTEXT_CATALOG N'FTS-CATALOGS', N'CREATE'
GO
----------***----------

-- Register the new table and certain columns in it for full-text search in
three languages.
EXEC SP_FULLTEXT_TABLE 'FTSTABLE', 'CREATE', 'FTS-CATALOGS', 'IX_FTSTABLE'
GO

EXEC SP_FULLTEXT_COLUMN 'FTSTABLE' , 'TEXT' , 'ADD' , '1033'
--ENGLISH
EXEC SP_FULLTEXT_COLUMN 'FTSTABLE' , 'TEXT' , 'ADD' , '1036'
--FRENCH
EXEC SP_FULLTEXT_COLUMN 'FTSTABLE' , 'TEXT' , 'ADD' , '1031'
--GERMAN

EXEC SP_FULLTEXT_TABLE 'FTSTABLE', 'ACTIVATE'
GO
----------***----------

-- Populate the new full-text catalog with full-text index information from
the new table.
IF EXISTS (SELECT * FROM DBO.SYSFULLTEXTCATALOGS WHERE NAME =
N'FTS-CATALOGS')
EXEC SP_FULLTEXT_CATALOG N'FTS-CATALOGS', N'START_FULL'
GO
----------***----------

-- For testing
-- Start full population of the full-text catalog. Note that it is
asynchronous, so delay must be built in if populating a large index.
WHILE (SELECT fulltextcatalogproperty('FTS-CATALOGS','populatestatus')) <> 0
BEGIN
WAITFOR DELAY '00:00:01' -- Check every 2 seconds to see if
full-text index population is complete.
CONTINUE
END
GO
----------***----------

-- Execute a full-text query against the new table
SELECT * FROM FTSTABLE WHERE CONTAINS(TEXT, ' "GHI" ')

Regards...

-----Original Message-----
From: Prasad Sombhatta [mailto:prasad.[Email Address Removed] Thursday, April 17, 2003 8:24 PM
To: LazyDBA.com Discussion
Subject: SQL Server 2000 full text indexing


Hi All,

What are the steps that I should follow to enable full text indexing on a
SQL Server 2000. Currently MS Search is not installed.

thanks


MS Sql Server LazyDBA home page