Support Center

Name is required.
Email address is required.
Invalid email address
Answer is required.
Exceeding max length of 5KB

Transaction error when creating Full Text Indexes

Chris Roberts Sep 29, 2013 11:54PM AEST

We have created a ready-roll script which attempts to create a full text index on some columns in a table.

However, when we try and deploy the script to a database, we get the error:
CREATE FULLTEXT INDEX statement cannot be used inside a user transaction

Up -8 rated Down
Daniel Nolan Sep 30, 2013 09:23PM AEST ReadyRoll Agent

Hi Chris,

Sorry that you’ve run into a problem with deploying your full-text indexes.

The error you are receiving is caused by the fact that ReadyRoll expects all T-SQL statements within Deploy-Once or Deploy-Change migrations to be transaction-capable. This is to ensure consistency of the database during deployment.

Unfortunately the downside of this is that any T-SQL statements that are not transaction capable, such as ALTER DATABASE statements or in this case Full-Text Indexes/Catalogs, must be executed through a Pre-Deployment or a Post-Deployment script. This means that the change must be written in such a way that it is repeatable, since these types of scripts are executed with every deployment.

As a suggestion, write your change as a Post-Deployment script and include conditional logic to only deploy the index if it doesn’t exist. For example:

IF NOT EXISTS (
SELECT 1
FROM sys.fulltext_indexes
WHERE object_id = object_id(‘Customers’)
)
CREATE FULLTEXT INDEX ON Customers (
FullName
,Description
) KEY INDEX idx_Customers;

Note that you may need additional logic if you wish to alter existing Full-Text indexes.

I realise this approach is far from ideal, however we are looking at introducing better support for deploying these types of objects in a future release.

Best Regards,

Daniel

This question has received the maximum number of answers.

readyroll.support@red-gate.com
http://assets2.desk.com/
false
readyrollsql
Loading
seconds ago
a minute ago
minutes ago
an hour ago
hours ago
a day ago
days ago
about
false
Invalid characters found
/customer/en/portal/articles/autocomplete