Cannot create MSSQL FULL TEXT INDEX using Liquibase

General Discussion
1

Hi,

SQL Server appears to constrain certain operations from being called within a user transaction. As a result, the following changeSet will not run from Liquibase:

    	<!-- TODO: Note this can't be called in a user transaction - must be called from command line! -->
	<sql> CREATE FULLTEXT INDEX ON freesearch ( value Language 0X0 ) KEY INDEX
		idx_freesearch_searchid ON ms_ft WITH CHANGE_TRACKING AUTO </sql>

</changeSet></ol>

    This will throw the following error:

      liquibase.exception.MigrationFailedException: Migration failed for change set C:/Workspace/CME-Dev/src/repository/schema/install/ind/freesearch.xml::BA19D99D-CD58-AECF-BE39-9C85C063831A::Alterian:     Reason: liquibase.exception.DatabaseException: Error executing SQL CREATE FULLTEXT INDEX ON freesearch ( value Language 0X0 ) KEY INDEX

                              idx_freesearch_searchid ON ms_ft WITH CHANGE_TRACKING AUTO:
                Caused By: Error executing SQL CREATE FULLTEXT INDEX ON freesearch ( value Language 0X0 ) KEY INDEX
                              idx_freesearch_searchid ON ms_ft WITH CHANGE_TRACKING AUTO:
                Caused By: CREATE FULLTEXT INDEX statement cannot be used inside a user transaction.
              at liquibase.changelog.ChangeSet.execute(ChangeSet.java:286)
              at liquibase.changelog.visitor.UpdateVisitor.visit(UpdateVisitor.java:27)
              at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:39)
              at liquibase.Liquibase.update(Liquibase.java:109)
              at liquibase.integration.ant.DatabaseUpdateTask.execute(DatabaseUpdateTask.java:45)

    Is there anyway that Liquibase could support a mechanism to allow such SQL to be run outside of a transaction? I will have to distribute a standalone Java app to create this index otherwise (or give users access to SQL Server’s sqlcmd tool).

    Thanks,

    Ben

2

There is a runInTransaction attribute on changeSet that you can set to false and it should not run in a transaction.

Nathan

3

That’s great. I’ll give it a go. Thanks

Ben