SSDT Deploy: Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'table' because it is not full-text indexed

Question

I'm deploying a dacpac to Azure as a part of a release pipeline using the "Azure SQL Database deployment" task.

I am getting the error: "Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'table' because it is not full-text indexed."

The table the dacpac is deployed to is an empty database that has been created every-time in a prior Azure CLI task and is dropped later in the deployment.

The issues appears to be an issue with the ordering...

Investigating the publish script shows:

Line 2489 - Creates the full text index catalog

Line 70374 - Create Stored Procedure - Fails Here

Line 80491 - Creates full text on table

So the stored procedure is created before the full text index it depends on.

I have tried:

Adding /p:IncludeTransactionalScripts=True in the "Additional SqlPackage.exe Arguments" (which made no difference as far as I can tell)
A pre-deployment script to manually add the full-text index before doing anything else. This failed too. Due to the fact that the table it needed to add the index too has yet to exist in the pre-deployment stage.

Temporary Work Around:

It's worth noting that I have got a temporary work around. In that I have the "Azure SQL Database deployment" task run twice. First time throwing the error but with "Continue on Error" selected and then second time around succeeding. This feel really hacky and bad though.

You might consider a Pre- pre-deployment task. That would create the indexes prior to the dacpac being processed. Alternatively, do a CREATE OR ALTER proc in a post-deployment task. That might work. It's not pretty, but if this is the only outlier, it might be the easier option. Create some shell of it in the project w/out the full-text bits or with those commented out with an explanation, then put the CREATE/ALTER in a post-deploy script to run. — Peter Schott, Aug 23 '19 at 18:52
Thanks but I tried a Pre-Deployment script but the table doesn't exist at that point so it doesn't have anything to add the index too. I realized I hadn't said about the database being empty each time so I have updated the description of the issue. — Jon Ryan, Aug 23 '19 at 19:09
Probably a post-deploy script for the Proc would make the most sense, then. Create some version of it without the full-text code in your project (with comments pointing to the full version if necessary). Do a CREATE OR ALTER post-deploy script for it. Perhaps even put a non-Build file in the project so nobody tries to update that - with the text being "CREATE PROC - change the code in the Post-Deploy section". — Peter Schott, Aug 23 '19 at 21:59

Lukasz Szozda · Answer 1 · 2019-08-22T20:23:53.283

One ugly way is using dynamic SQL inside stored procedure:

CREATE PROCEDURE proc(@desc NVARCHAR(100))
AS
BEGIN
  SELECT * FROM tab WHERE CONTAINS(col, @desc);
END;

=> 
CREATE PROCEDURE proc(@desc NVARCHAR(100))
AS
BEGIN
  EXEC sp_executesql 
       N'SELECT * FROM tab WHERE CONTAINS(col, @desc)', 
       N'@desc NVARCHAR(100)', 
       @desc;
END;

Drawbacks:

no object checks(it is not a static code)
context switch(may introduce permission problems)

SSDT Deploy: Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'table' because it is not full-text indexed

1 Answers1