Stored procedure depends on Full-text Index, can't compare

TimVervers · September 21, 2016 9:11AM

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 &#91;dbo&#93;.&#91;pr_Search&#93;
  @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 &gt;= @param3
	AND (ISNULL(@param4,'')='' OR b.TimeStart&lt;=@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 &#91;KEY&#93; 
		FROM CONTAINSTABLE(Table3, (TicketNumber, ReferenceDisplay), @search) data
		JOIN Table3 b ON data.&#91;KEY&#93;=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.&#91;KEY&#93;				
	) 
	ORDER BY b.TimeStart
END

mattchandler · September 22, 2016 9:40AM

Hi Tim,

Sorry you've been seeing this problem. I can reproduce your problem here and you are quite right that the issue seems to be creating the procedure before full text indexes have been added to referenced tables. I will add an issue to our internal bug tracking system about this. Immediately the only work-around for this I can see is to pre-populate your target database with either the full text tables or the stored procedure (interestingly SQL Server allows creating stored procedures that reference objects that don't exist - so I can run your create procedure script with none of the referenced tables). If you are using SQL Source Control then migrations scripts might be a way to do this.

Matthew Chandler
Software Developer on SQL Compare and SQL Data Compare

Sergio R · April 27, 2018 10:19AM

To avoid this issue, you can use the SQL Compare option NoTransactions:
https://documentation.red-gate.com/sc13/using-the-command-line/options-used-in-the-command-line#Optionsusedinthecommandline-NoTransactions

Stored procedure depends on Full-text Index, can't compare

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk