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
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 (
WHERE object_id = object_id(‘Customers’)
CREATE FULLTEXT INDEX ON Customers (
) 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.
This question has received the maximum number of answers.