Issue with Full Text Index

Packages and compresses schema and contents of SQL Server databases

Moderators: JonathanWatts, Chris Auckland, David Atkinson, david connell, Anu Deshpande, Michelle Taylor, james.billings, Marianne

Issue with Full Text Index

Postby jbanta » Sat Feb 27, 2010 3:38 pm

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.
jbanta
 
Posts: 2
Joined: Sat Feb 27, 2010 3:30 pm
Location: Las Vegas, NV

Postby Michelle Taylor » Mon Mar 01, 2010 12:22 pm

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

Postby jbanta » Mon Mar 01, 2010 8:29 pm

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.
jbanta
 
Posts: 2
Joined: Sat Feb 27, 2010 3:30 pm
Location: Las Vegas, NV

Full Text Index problem

Postby BobH » Wed Nov 03, 2010 10:57 pm

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!
BobH
 
Posts: 1
Joined: Wed Nov 03, 2010 10:50 pm


Return to SQL Packager 6

Who is online

Users browsing this forum: No registered users and 0 guests