Import Organization does not rename the full text catalog ftcat_documentindex_ with the new OrganizationId in Microsoft Dynamics CRM 4.0

Symptoms

After you import your organization by using Deployment Manager, a system job of FullTextCatalogIndex in the imported organization stops working and stays in a waiting status. If you applied KB977867, you won't have the issue.

Cause

Import organization does not rename the full text catalog and the imported organization database has stored the full text catalog which contains the backed-up organization's GUID. System jobs try to execute the full text catalog update which contains the current organization id ftcat_documentindex_<OrganizationIdGuidValue> and then changes to waiting status.

Resolution

In order to resolve this issue run the following SQL script on your organization database:

DECLARE @ID AS NVARCHAR(128), 
  @OLDNAME AS NVARCHAR(128), 
  @NEWNAME AS NVARCHAR(128),
  @LINES AS INT
SELECT @OLDNAME = name FROM  SYS.FULLTEXT_CATALOGS WHERE name LIKE 'ftcat_documentindex_%'
SELECT @ID = OrganizationId FROM Organization
SET @ID = REPLACE(@ID,'-','')
SET @ID = LOWER(@ID)
SET @NEWNAME = 'ftcat_documentindex_' + @ID
PRINT 'GUID IS : '+@ID
PRINT '---------------------'
PRINT 'OLDNAME IS : '+@OLDNAME
PRINT '---------------------'
PRINT 'NEWNAME IS : '+@NEWNAME
PRINT '---------------------'
IF @OLDNAME!=@NEWNAME
BEGIN
 EXEC sp_fulltext_database 'enable' 
 -- Drop exsiting full text catalog
 PRINT 'START DROPPING EXSITING FULL TEXT CATALOG ..'
 PRINT '---------------------'
 IF  EXISTS (SELECT * FROM sys.fulltext_indexes fti WHERE fti.object_id = OBJECT_ID(N'[dbo].[DocumentIndex]'))
 BEGIN
  ALTER FULLTEXT INDEX ON [dbo].[DocumentIndex] DISABLE
 END
 IF  EXISTS (SELECT * FROM sys.fulltext_indexes fti WHERE fti.object_id = OBJECT_ID(N'[dbo].[DocumentIndex]'))
 BEGIN
  DROP FULLTEXT INDEX ON [dbo].[DocumentIndex]
 END
 SELECT @LINES = COUNT(*) FROM sysfulltextcatalogs ftc WHERE ftc.name = @OLDNAME
 IF  @LINES > 0
 BEGIN
  EXEC sp_fulltext_catalog @OLDNAME,'drop' 
 END
 PRINT 'DROPPING COMPLETE ..'
 PRINT '---------------------'
 -- Add new full text catalog
 PRINT 'START ADDING EXSITING FULL TEXT CATALOG ..'
 PRINT '---------------------'
 EXEC sp_fulltext_catalog @NEWNAME,'create'
 EXEC sp_fulltext_table 'documentindex', 'create', @NEWNAME, 'cndx_PrimaryKey_DocumentIndex' 
 EXEC sp_fulltext_column 'documentindex', 'Title', 'add' 

 EXEC sp_fulltext_column 'documentindex', 'KeyWords', 'add' 

 EXEC sp_fulltext_column 'documentindex', 'SearchText', 'add' 

 EXEC sp_fulltext_table 'documentindex', 'activate' 

 EXEC sp_fulltext_catalog @NEWNAME, 'start_full' 
 PRINT 'ADDING COMPLETE ..'
END
ELSE
PRINT 'THE FULL TEXT CATALOG HAS BEEN UPDATED ..'