Full-text indexing

• sudheer_d123

  Newbie

  Points: 1

  More actions

  August 9, 2003 at 1:23 am

  #82509

  Please anybody can help about SQLServer full-text indexing.

  I was running below example:

  but no where i'm finding the output.

  USE pubs

  -- Create and populate a table.

  IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES

  WHERE TABLE_NAME = 'FulltextTest')

  DROP TABLE FulltextTest

  GO

  CREATE TABLE FulltextTest

  ( article_id int IDENTITY(100,1)

  CONSTRAINT PK_title_id PRIMARY KEY,

  article_title nvarchar(200)

  )

  GO

  INSERT FulltextTest (article_title) VALUES (N'Steven Buchanan has always enjoyed ice skating.')

  INSERT FulltextTest (article_title) VALUES (N'Elvis Stoiko: The best male figure skater')

  INSERT FulltextTest (article_title) VALUES (N'Steven Buchanan On Ice: Skating Reaches Tops in Public Opinion Poll')

  INSERT FulltextTest (article_title) VALUES (N'Last night, Steven Buchanan skated on the ice!! Skating fans cheer!')

  INSERT FulltextTest (article_title) VALUES (N'Ice-skating brings out the best in Steven. Buchanan exults in first victory...')

  GO

  -- Enable full-text searching in the database.

  EXEC sp_fulltext_database 'enable'

  GO

  -- Create a new full-text catalog.

  EXEC sp_fulltext_catalog 'StevenBCatalog',

  'create'

  GO

  -- Register the new table and column within it for full-text querying,

  -- then activate the table.

  EXEC sp_fulltext_table 'FulltextTest',

  'create',

  'StevenBCatalog',

  'PK_title_id'

  EXEC sp_fulltext_column 'FulltextTest',

  'article_title',

  'add'

  EXEC sp_fulltext_table 'FulltextTest',

  'activate'

  GO

  -- Start full population of the full-text catalog. Note that it is

  -- asynchronous, so delay must be built in if populating a

  -- large index.

  EXEC sp_fulltext_catalog 'StevenBCatalog',

  'start_full'

  WHILE (SELECT fulltextcatalogproperty('StevenBCatalog',

  'populatestatus')) <> 0

  BEGIN

  WAITFOR DELAY '00:00:02' -- 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 article_title

  FROM FulltextTest

  WHERE CONTAINS(article_title, ' "Steven Buchanan" AND "ice skating" ')

  select * from fulltexttest where article_title like '%best%' or article_title like 'ice%skating%'

• mccork

  SSC-Insane

  Points: 22288

  More actions

  August 9, 2003 at 3:40 pm

  #469289

  It could be because you have remove BUILTIN\Administrators from login. In which case you see an item count of zero when you view the properties of the FT (StevenBCatalog) catalog.

  Either add it back in as a SQL system admin, or add [NT Authority\System] as a system admin, then rebuild and repopulate the catalog, then refresh your EM view. You should then see an item count of 6, and your CONTAINS query should then work.

  Also see KB: http://support.microsoft.com/default.aspx?scid=KB;EN-US;q317746

  Cheers,

  - Mark

  
  Cheers,
  - Mark