FTI problems: Cannot use a CONTAINS or FREETEXT predicate

Forum for users of SQL Compare schema synchronization utility

FTI problems: Cannot use a CONTAINS or FREETEXT predicate

Postby rvw » Wed May 14, 2008 12:20 pm

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?
rvw
 
Posts: 8
Joined: Mon Nov 19, 2007 3:21 pm

Postby Michelle Taylor » Wed May 14, 2008 1:41 pm

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.
Michelle Taylor
 
Posts: 535
Joined: Mon Oct 30, 2006 12:45 pm
Location: Red Gate Software


Return to SQL Compare Previous Versions

Who is online

Users browsing this forum: No registered users and 0 guests