I'm using DLM to automatically deploy my database changes to a testing environment.
But I'm running into the following issue:
'MyDatabase_RedGate_DLM' failed: Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'Table3' because it is not full-text indexed.
Error executing the following SQL: CREATE PROCEDURE [dbo].[pr_Search] @param1 INT, @param2 VARCHAR(50), @param3 DATETIME, @param4 DATETIME = NULL, @param5 VARCHAR(1000) = ''... 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 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 ##[error]SQL CI exitcode was 32. See console output for details.
It feels like the Stored Procedure is being created BEFORE the table is being full-text indexed. When i print the script, place the CREATE PROCEDURE at the end of the script, it works fine.
How can i resolve this issue to keep deploying our database automatically?
Tim
Here is an example of our Stored Procedure:
CREATE PROCEDURE [dbo].[pr_Search]
@param1 INT,
@param2 VARCHAR(50),
@param3 DATETIME,
@param4 DATETIME = NULL,
@param5 VARCHAR(1000) = '', --TODO
@param6 VARCHAR(4000) = '',
@param7 VARCHAR(4000) = '',
@param8 INT = 0
AS
BEGIN
DECLARE @param9 TABLE (id int)
DECLARE @param10 TABLE (id int)
DECLARE @search VARCHAR(50) = '"'+@param2+'*"'
-- split into table: join for id first
IF @param6 != '' BEGIN
INSERT INTO @param9
SELECT id FROM fn_SplitValues(@param6) data
JOIN Table1 p ON data.value=p.Guid
END
-- split into table: join for id first
IF @param7 != '' BEGIN
INSERT INTO @param10
SELECT id FROM fn_SplitValues(@param7) data
JOIN Table2 l ON data.value=l.Guid
END
SELECT b.id, b.TimeStart, b.ReferenceDisplay, b.TicketNumber, b.FK_BookUserId, l.GUID Table2Guid
FROM Table3 b
JOIN Table2 l ON b.FK_Table2Id=l.Id
JOIN Table4 r ON l.FK_Table4Id=r.Id
WHERE r.FK_DomainId=@param1
AND b.TimeStart >= @param3
AND (ISNULL(@param4,'')='' OR b.TimeStart<=@param4+1)
AND (@param8=0 OR b.FK_BookUserId=@param8)
AND (ISNULL(@param7, '')='' OR b.FK_Table2Id IN (SELECT id FROM @param10))
AND (ISNULL(@param6, '')='' OR EXISTS (
SELECT 1 FROM Table3Item bi
JOIN @param9 fp ON bi.FK_Table1Id=fp.id
WHERE b.Id=bi.FK_Table3Id )
)
AND b.id IN (
SELECT [KEY]
FROM CONTAINSTABLE(Table3, (TicketNumber, ReferenceDisplay), @search) data
JOIN Table3 b ON data.[KEY]=b.id
UNION
SELECT b.Id FROM Table3 b
JOIN Table5 d ON b.FK_Table5Id=d.Id
JOIN CONTAINSTABLE(Table6, (Lastname, MailAddress), @search) data ON d.CustomerUserId=DATA.[KEY]
)
ORDER BY b.TimeStart
END
Hi Tim,
But I'm running into the following issue:
'MyDatabase_RedGate_DLM' failed: Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'Table3' because it is not full-text indexed.
Error executing the following SQL: CREATE PROCEDURE [dbo].[pr_Search] @param1 INT, @param2 VARCHAR(50), @param3 DATETIME, @param4 DATETIME = NULL, @param5 VARCHAR(1000) = ''... 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 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 ##[error]SQL CI exitcode was 32. See console output for details.
It feels like the Stored Procedure is being created BEFORE the table is being full-text indexed. When i print the script, place the CREATE PROCEDURE at the end of the script, it works fine.
How can i resolve this issue to keep deploying our database automatically?
Tim
Here is an example of our Stored Procedure: