Can't get FTS to work correctly

• VegaMachine

  SSCrazy

  Points: 2080

  More actions

  July 28, 2003 at 12:45 pm

  #59082

  This is happening on all the servers I've tested on including fresh installs w/sp3. It's gotta be something I'm missing.

  Microsoft Search is running and I execute the following in QA (An example pulled from this site)

  --Implementing Full Text Search with T-SQL stored Procedures / Eli Leiba

  Enabeling Full Text search in T-SQL is usually not so "popular" as doing it with the EnterPrize Manager

  So Here are the T-SQL steps you have to do in order to implement FTS in T-SQL

  --1 Enabling full text on the database

  EXEC sp_fulltext_database 'enable'

  --2 Create the Catalog (if does not exist)

  EXEC sp_fulltext_catalog 'MyCatalog','create'

  --3 add a full text index on a Table

  EXEC sp_fulltext_table 'Products', 'create', 'MyCatalog', 'pk_products'

  EXEC sp_fulltext_table 'Categories', 'create', 'MyCatalog', 'pk_categories'

  --4 add a column to the full text Index

  EXEC sp_fulltext_column 'Products', 'ProductName', 'add'

  EXEC sp_fulltext_column 'Categories', 'Description', 'add'

  --5 activate the index

  EXEC sp_fulltext_table 'Products','activate'

  EXEC sp_fulltext_table 'Categories','activate'

  --6 start full population

  EXEC sp_fulltext_catalog 'MyCatalog', 'start_full'

  -- usage in T-SQL (CONTAINS and FREETEXT predicates)

  -- Using the index in T-SQL

  USE Northwind

  GO

  SELECT ProductId, ProductName, UnitPrice

  FROM Products

  WHERE CONTAINS(

  ProductName, ' "sasquatch " OR "stout" '

  )

  GO

  USE Northwind

  GO

  SELECT CategoryName

  FROM Categories

  FREETEXT (

  Description, 'sweetest candy bread and dry meat'

  )

  GO

  I execute line by line and all goes well except for I get 0 results returned to me when I execute (after status goes to idle). Also the top 4 option when I right click on Full-Text Catalogs are greyed out and when I try to delete the catalog it tells me the full-test service must be running, which I think it is (microsoft search is running). Any ideas? I've also followed the tutorials in BOL and get the same 0 results returned.

  Thanks in advance,

  - Vega

  
  
  - Vega

• July 31, 2003 at 8:00 am

  This was removed by the editor as SPAM

• mccork

  SSC-Insane

  Points: 22288

  More actions

  July 31, 2003 at 5:16 pm

  #468113

  I encountered a similar problem on a box recently. Despite being connected as sa, I didn't have any administrative rights on the server.

  I don't know if the cause your problem is similar, but just a shot.

  Cheers,

  - Mark

  
  Cheers,
  - Mark

• VegaMachine

  SSCrazy

  Points: 2080

  More actions

  August 1, 2003 at 7:11 am

  #468191

  That is exactly what seems to be happening. No matter how I log in, it will not allow me to use EM to create any Full-Text catalogs. Strange thing is that it's happen on an enterprise SQL box, a development edition box and on a standard box. Did you resolve the issue at all?

  - Vega

  
  
  - Vega

• mccork

  SSC-Insane

  Points: 22288

  More actions

  August 1, 2003 at 2:34 pm

  #468286

  I obtained a connection to administrative share (C$) on the server from from workstation (the administrator supplied the password). Initially no success, but after a series of steps which I think involved restarting down EM, I got the FT indexing ability back.

  Sorry about the imprecise response, but sometimes when things go right one doesn't really know (or care) why.

  

  Cheers,

  - Mark

  
  Cheers,
  - Mark

• rschaeferhig

  SSCrazy Eights

  Points: 8776

  More actions

  September 2, 2004 at 9:15 am

  #521530

  I know this is an old thread, but I was searching for anyone with similar problems hoping someone has a resolution. The problem is described here:

  http://support.microsoft.com/default.aspx?scid=KB;en-us;q270671

  Basically you either have to have to be a Windows Local Administrator on the server (servers if it's a cluster) in order to use EM to set up FT on a database. Once FT is set up you can use EM to manage the existing catalogs without having Local Admin. It's just the FT Wizard that apparently requires Local Admin authority.

  The alternative as described in the above MS article is to use the sp_fulltext stored procedures. Apparently they don't check the MSSearch service status or try to manipulate it so they don't need Local Admin.

  One would think MS would separate the ability to start and stop FT at the server level from the ability to full text index databases, create catalogs, etc, but apparently that thought hadn't crossed their mind. Had they done so Local Admin would only be required to start and stop the FT service (MSSearch), not to full text index a database.

  Oh well...