Issue with Full Text Index

jbanta

An inherit problem with Sql Packager and Sql Compare / Data Compare occurs when having stored procedures that reference a full text index.

The stored procedures use the CONTAINS() clause against a full text index table.

When using Sql Compare, the application will try to install the stored procedures first and fail every time since the table is not setup as a full text index yet.

This requires a two step process in Sql Compare where I run the compare for the full text indexes, first. Then run the rest and this works ok.

But this problem renders Sql Packager usless since I cannot get the process to run once at all.

Please update the applications to:

1
Optionally call: exec sp_fulltext_database 'enable'.

2.
Create full text indexes before the stored procedures.

By simply changing the order of execution, this should solve the problem.

Thanks,
Jon B.

Michelle T

The problem is, SQL Server doesn't allow full text indexing operations within transactions. So if the full text indexing statements occur before the stored procedure statements, then something fails further down the script, the database may be left in an inconsistant state.

We're keeping track of how many times this query comes up, and in a future version we might implement an option which explains the trade-off being made - the tracking number for the request is SC-1196.

jbanta

I understand that Full Text changes are outside of a transaction, and am willing to adapt to adhere to this.

Even when trying to separate Full text logic into a separate package this does not work since both package will pull the same dependencies. The first package will run, but the second will fail every time trying to add something that is already there.

A suggested feature would be to simply have a pre and post script for the package, with the option of the scripts being within the transaction or not.

Tasks like enabling Full Text Indexes, or population scripts would be used here and would limit the number of packages to only one.

This would alleviate many issues/drawbacks that makes the application useless for some individuals.

Please keep us informed of the progress of this feature.

BobH

I'm having the problem where SQL Packager 6 builds a stored proc that uses CONTAINSTABLE before SQL Packager 6.0 builds the necessary Full-text index.

You indicated that if enough people experience the problem, you might implement a solution (of sorts). I wanted to go on record as also suffering from the problem.

Thanks!