Rebuild MS SQL full text catalogs for commerce server 2007 search

Posted in Commerce Server, MS SQL

If you are getting unexpected search results from you commerce server web application it could be caused by your SQL Server full text catalog indexs not been rebuilt. We experienced this issue while using the CS2007  agent APIs to created/modify virtual catalogs. The last step in the process triggers the rebuild of all the indexes using this SQL: 

[sql]
DECLARE @Rows int
DECLARE @Name varchar(8000)
DECLARE @FullTextCatalogs TABLE (i int IDENTITY , Name varchar(8000))

INSERT INTO @FullTextCatalogs
SELECT name
FROM [database_productcatalog].sys.fulltext_catalogs

SELECT @Rows = @@ROWCOUNT

WHILE @Rows > 0
BEGIN
    Select @Name = [Name] from @FullTextCatalogs WHERE i = @Rows
    EXECUTE(‘USE [database_productcatalog]; ALTER FULLTEXT CATALOG [‘ + @Name + ‘] REBUILD’)
    SET @Rows = @Rows -1
END
[/sql]

Published by Kazim Nami