Red Gate forums :: View topic - Issue with Full Text Index
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Packager 6
SQL Packager 6 forum

Issue with Full Text Index

Search in SQL Packager 6 forum
Post new topic   Reply to topic
Jump to:  
Author Message
jbanta



Joined: 27 Feb 2010
Posts: 2
Location: Las Vegas, NV

PostPosted: Sat Feb 27, 2010 3:38 pm    Post subject: Issue with Full Text Index Reply with quote

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.
Back to top
View user's profile Send private message
Michelle Taylor



Joined: 30 Oct 2006
Posts: 525
Location: Red Gate Software

PostPosted: Mon Mar 01, 2010 12:22 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
jbanta



Joined: 27 Feb 2010
Posts: 2
Location: Las Vegas, NV

PostPosted: Mon Mar 01, 2010 8:29 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
BobH



Joined: 03 Nov 2010
Posts: 1

PostPosted: Wed Nov 03, 2010 10:57 pm    Post subject: Full Text Index problem Reply with quote

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!
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group