FTI problems: Cannot use a CONTAINS or FREETEXT predicate

rvw

I have the following problem. I want to update a database from a development version, and many times I get the following error message:

[7601] Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'abc' because it is not full-text indexed.

Up til now I've always managed to get it working in the end, but it is a pain. The first action of the compare script is to drop the full text indexes, the one that is referenced here. So after this error message, I need to recreate the FTI for this table. Then I run the compare, the FTI is dropped, the error message appears, I recreate the FTI, etc.

Then somehow, after many tries, something changes and I can update the database, but not the problematic tables. When that happens, I need to repeat the steps one or two times again, and then the update is complete.

This is very annoying. I've been looking for the reason why it suddenly does work, but haven't found it. Can you recommend steps I can take to avoid these problems and run the compare in a predictable manner?

Michelle T

It doesn't work because full text indexes can't be updated transactionally, so we do them all at the end of the script, after the transaction. Unfortunately that means that if you have something which relies on them in the middle of the script, then it will fail with the error you're seeing.

The workaround is to synchronize the fulltext catalogs / tables with fulltext indexes first, and then synchronize the rest of the database.