CI: "Cannot use a CONTAINS or FREETEXT predicate..."
I'm evaluating the DLM tools to provide a CI mechanism for updating the various databases (Patient and Hospital data, Dept. of Health) across our different environments.
I currently have the build server set up to perform the automatic build, test and sync. operations off the back of changes made via TFS. However, I am unable to get past a problem on a test database which uses full text search.
The build server is set up to use a local SQL Server instance and full text search has been installed and enabled on the instance.
Unfortunately, when the scratch database is being constructed by the CI build I'm getting the following error...
Can anyone shed any light on this, please?
I currently have the build server set up to perform the automatic build, test and sync. operations off the back of changes made via TFS. However, I am unable to get past a problem on a test database which uses full text search.
The build server is set up to use a local SQL Server instance and full text search has been installed and enabled on the instance.
Unfortunately, when the scratch database is being constructed by the CI build I'm getting the following error...
Checking for identical databases
Creating SQL
Inserting static data SQL into deployment script
Deploying changes (from DB1 to DB2)
1>d:Builds27FAQFAQ SQL Sync Dev to TestsrcDevRedGateSQLScripts.sqlciproj(148,5): error : Error: Synchronization of 'Scripts.state' and
1>d:Builds27FAQFAQ SQL Sync Dev to TestsrcDevRedGateSQLScripts.sqlciproj(148,5): error : 'tfs-bldagnt-poc.sqlCI_d3fa3ddb-b5da-4bf1-b6c2-8b06af43d610' failed: Cannot use
1>d:Builds27FAQFAQ SQL Sync Dev to TestsrcDevRedGateSQLScripts.sqlciproj(148,5): error : a CONTAINS or FREETEXT predicate on table or indexed view 'Answer' because it is
1>d:Builds27FAQFAQ SQL Sync Dev to TestsrcDevRedGateSQLScripts.sqlciproj(148,5): error : not full-text indexed. Error executing the following SQL: CREATE FUNCTION
1>d:Builds27FAQFAQ SQL Sync Dev to TestsrcDevRedGateSQLScripts.sqlciproj(148,5): error : [dbo].[udf_AnswerSearch] (@keyWords NVARCHAR(4000)) RETURNS TABLE AS RETURN
1>d:Builds27FAQFAQ SQL Sync Dev to TestsrcDevRedGateSQLScripts.sqlciproj(148,5): error : (SELECT [KEY] AS AnswerID, [Rank] as Rank FROM FREETEXTTABLE(Answer, AnswerText,
1>d:Builds27FAQFAQ SQL Sync Dev to TestsrcDevRedGateSQLScripts.sqlciproj(148,5): error : @...
1>d:Builds27FAQFAQ SQL Sync Dev to TestsrcDevRedGateSQLScripts.sqlciproj(148,5): error : sqlCI error occurred: Validating database state failed with error: SQLCompare failed with exit code 126: see output above for more information, or for generic information about this exit code, see: http://www.red-gate.com/sqlCI/ExitCodes/SQLCompare
FINISHED WITH ERROR: Validating database state
1>d:Builds27FAQFAQ SQL Sync Dev to TestsrcDevRedGateSQLScripts.sqlciproj(148,5): error : sqlCI error occurred: Running SQLCompare failed with error: SQLCompare failed with exit code 126: see output above for more information, or for generic information about this exit code, see: http://www.red-gate.com/sqlCI/ExitCodes/SQLCompare
Build step "Build" finished.
1>Done Building Project "d:Builds27FAQFAQ SQL Sync Dev to TestsrcDevRedGateSQLScripts.sqlciproj" (default targets) -- FAILED.
Build FAILED.
<snip>
I can see the FTS catalog create script in the StorageFull Text Catalogs folder of the sqlciproj...
CREATE FULLTEXT CATALOG [FAQQuestionsFTS] WITH ACCENT_SENSITIVITY = OFF AUTHORIZATION [dbo] GO...and the corresponding FTS index being set up on the table...
CREATE TABLE [dbo].[Answer] ( [AnswerID] [int] NOT NULL IDENTITY(1, 1), [QuestionID] [int] NOT NULL, [AnswerText] [nvarchar] (1000) COLLATE Latin1_General_CI_AS NOT NULL, <snip> GO CREATE FULLTEXT INDEX ON [dbo].[Answer] KEY INDEX [PK_dbo.Answer] ON [FAQQuestionsFTS] GO ALTER FULLTEXT INDEX ON [dbo].[Answer] ADD ([AnswerText] LANGUAGE 1033) GO...but the error would suggest the TFS is not correctly configured on the table by the time the FTS functions are being built.
Can anyone shed any light on this, please?
Comments
Full-text objects cannot be created within a transaction, so the way we deal with full-text objects is to put them at the end of the script, after any transactions. Unfortunately, doing this means that if any objects being created within a transaction depend on the full text objects then you'll get this error. Until Microsoft allow us to create full text objects within a transaction then there's not much we can do.
For the build step, if you tell the SQL Compare engine to not use transactions then this should get around the problem. Using the NoTransactions option during the build step should not be a problem. You are building to a temporary database so a failure won't impact anything. Also, the build process empties the database at the start so it won't leave you with any problems.
You can add the NoTransactions option by going to your .sqlciproj file and clicking "Edit" on the build step. In the Advanced tab there is a textbox labelled "Include SQL Compare arguments". In this texbox, enter /Options:NoTransactions
For deployments, it would be best to avoid using the NoTranactions option. Most of the time the deployment will be successful. It's only in this specific case of deploying both a full text object and another object that depends on the full text object that you'll get a failure. If that happens then you might want to deploy manually using SQL Compare.
I hope this helps.
Redgate Software
Thanks
That's great to hear! Thanks for letting me know.
Redgate Software