When performing an update today to a development environment i hit the following error when attempting to upgrade the Organization DB. I have a couple on the dev DB in this case one of them would not update.

In the log i found the following error message.

Update Organization with Id=<snipped> failed with Exception:
System.Exception: Error.ActionFailed Microsoft.Crm.Tools.Admin.InstallDatabaseUpdatesAction —> System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. —> System.Data.SqlClient.SqlException: Cannot drop index ‘cndx_PrimaryKey_OpportunityProduct’ because it enforces the full-text key for table or indexed view ‘OpportunityProductBase’.

 

Apparently, the release is still a bit buggy so i wanted to share this for any other users out there. If you manually drop the indexes before you upgrade the DB in deployment manager it will continue on. It appears to be a sequencing issue with the update wizard. If you are not comfortable working in SQL and CRM i would reccomend filing a support incident. If you are handy with SQL there here is what you do next. Also, but keep in mind that your indexes will be rebuilt which will make it a little slow until they rebuild.

1.) First we need to create a copy of all the indexes in place so that they can be recreated at the end of this process. Do this by running the following query. Export the output of this script and save it in a safe location. Name it recreateindexes.sql or something relevant.

 

declare @catid int
select @catid=fulltext_catalog_id from sys.fulltext_catalogs where name=’CRMFullTextCatalog’
declare c cursor for
select sys.tables.name, sys.fulltext_indexes.unique_index_id from sys.fulltext_indexes inner join sys.tables on sys.fulltext_indexes.object_id = sys.tables.object_id where sys.fulltext_indexes.fulltext_catalog_id=@catid
open c
declare @TableName varchar(200), @UniqueID as integer
fetch next from c into @TableName, @UniqueID
while @@fetch_status = 0
begin
declare d cursor for
select sys.indexes.name, sys.tables.object_id from sys.tables inner join sys.indexes on sys.tables.object_id = sys.indexes.object_id where sys.tables.name=@TableName and sys.indexes.index_id = @UniqueID
open d
declare @KeyIndex varchar(200), @object_id as integer
fetch next from d into @KeyIndex, @object_id
if @@FETCH_STATUS <> 0
begin
Print ‘Error with’ + @TableName
end
while @@fetch_status = 0
begin
BEGIN TRY
Print ‘CREATE FULLTEXT INDEX ON [dbo].’+@TableName+’ KEY INDEX [‘+@KeyIndex+’] on([CRMFullTextCatalog]) WITH (CHANGE_TRACKING AUTO)’
Print ‘GO’
declare e cursor for
select sys.columns.name from sys.columns inner join sys.fulltext_index_columns on sys.columns.object_id=sys.fulltext_index_columns.object_id and sys.columns.column_id=sys.fulltext_index_columns.column_id where sys.columns.object_id=@object_id
open e
declare @ColumnName varchar(200)
fetch next from e into @ColumnName
while @@fetch_status = 0
begin
Print ‘ALTER FULLTEXT INDEX ON [dbo].’+@TableName+’ Add (‘+@ColumnName+’)’
Print ‘GO’
fetch next from e into @ColumnName
end
close e
deallocate e
END TRY
BEGIN CATCH
print ‘Error’ + @KeyIndex
END CATCH
fetch next from d into @KeyIndex, @object_id
end
close d
deallocate d
fetch next from c into @TableName, @UniqueID
end
close c
deallocate c

2.) Only after you have saved the output run the next script below and it will create the output for DROPPING the indexes from your Organization DB.

declare @catid int

select @catid=fulltext_catalog_id from sys.fulltext_catalogs where name=’CRMFullTextCatalog’

declare c cursor for

select sys.tables.name, sys.fulltext_indexes.unique_index_id from sys.fulltext_indexes inner join sys.tables

on sys.fulltext_indexes.object_id = sys.tables.object_id where sys.fulltext_indexes.fulltext_catalog_id=@catid

open c

declare @TableName varchar(200), @UniqueID as integer

fetch next from c into @TableName, @UniqueID

while @@fetch_status = 0

begin

declare d cursor for

select sys.indexes.name, sys.tables.object_id from sys.tables inner join sys.indexes

on sys.tables.object_id = sys.indexes.object_id where sys.tables.name=@TableName and sys.indexes.index_id = @UniqueID

open d

declare @KeyIndex varchar(200), @object_id as integer

fetch next from d into @KeyIndex, @object_id

if @@FETCH_STATUS <> 0

begin

Print ‘Error with’ + @TableName

end

while @@fetch_status = 0

begin

BEGIN TRY

Print ‘DROP FULLTEXT INDEX ON [dbo].’+@TableName+’;’

Print ‘GO’

END TRY

BEGIN CATCH

print ‘Error’ + @KeyIndex

END CATCH

fetch next from d into @KeyIndex, @object_id

end

close d

deallocate d

fetch next from c into @TableName, @UniqueID

end

close c

deallocate c

 

3.) Run the output that is generated against your CRM OrganizationDB and it will drop all the indexes needed.

4.) Now, go back into deployment manager and select to update the organization and it should complete this time.

5.) Once the update completes (it will take a while if you have a large db), the full text indexes back to CRM. You do this
by running the output from the script you created on step 1. The script should be named recreateindexes.sql or something similar.