Cannot use CONTAINS predicate ... not full-text indexed

juneinfo

I have sucessfully created an excutable but when I try to create or upgrade a database I get the error "Cannot use CONTAINS or FREETEXT predicate on table XXX because it is not full-text indexed."

The table has full-text indexing enabled. I have tried full population but that did not help. Any suggestion on what I might be doing wrong?

I'm using SQL Packager 1.1.2.5

Brian Donahue

Hello June,

There is a bit of a problem with doing full-text indexes in SQL Packager because SQL Server does not allow the creation of full-text indexes inside a transaction. The schema scripts being written in transact-SQL means that the full-text scripting has to be done at the very end of the script after the transactions have all committed.

The problem that this introduces is that any stored procedures in the script that use CONTAINS will fail because the underlying tables will not be full-text indexed until the very end.

The only solution that I can think of is to create two packages: One will contain all of the tables, data, and full-text catalogs, and the second will contain views and stored procedures.

Hopefully this will work in your case.

LarsBuur

Could one just edit the generated resourcefiles - and make to transactions - one with all the generation of tables, sprocs, full-text indexes etc. - and then an new transaction in the same resource file - and at the bottom with the sprocs with the contains keyword?

Kind regards

Lars Buur

Brian Donahue

Hi Lars,

That's one possibility. You could add a SQL batch into the resources, but it would need to be properly constructed. I'd dealt with someone who was trying to do this yesterday and it caused some difficulty.

Another possibility is to add the code to the PackageExecutor.cs class before the loop that executes all of the batches.