Cannot use a CONTAINS or FREETEXT predicate on table

cja100

I'm trying to use sql compare but I cannot get around the problem below, anyone have any ideas?

The following error message was returned from the SQL Server:

[7601] Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'tSearchData' because it is not full-text indexed.

The following SQL command caused the error:

ALTER PROCEDURE dbo._CheckRulesBeforeDelete
(
	@nUserId int, 
	@nNodeId int,
	@nReturn int output
)
AS

	SET NOCOUNT ON

	SET @nReturn = 0

	DECLARE @strObjectType nvarchar(50)
	DECLARE @intParentId int
	DECLARE @ObjectId int
	DECLARE @sObjectId nvarchar(50)
	DECLARE @sSearchString nvarchar(200)
	DECLARE @sSearchString2 nvarchar(200)
	DECLARE @sObjectValue varchar(50)
	DECLARE @nWBHits int
	DECLARE @nRBHits int

	-- Kollar så att man inte försöker ta bort UserGroup eller UserRoot
	IF EXISTS (SELECT     tTreeNesting.ParentId, tTreeNesting.ChildKey, tTreeNesting.Nesting, _vwUserGroupsAndUsers.UserName
		        FROM         tTree 
				INNER JOIN	tTreeNesting ON tTree.NodeId = tTreeNesting.ChildKey 
				INNER JOIN	_vwUserGroupsAndUsers ON tTree.ObjectId = _vwUserGroupsAndUsers.UserId
		        WHERE     (tTreeNesting.ParentId = @nNodeId))
	BEGIN
		SET @nReturn = -1
		RETURN
	END
	
	-- Kollar så man inte försöker ta bort nod med PabloObject som barn
	IF EXISTS (SELECT     tTreeNesting.ParentId
	           FROM         tTree 
				INNER JOIN	tTreeNesting ON tTree.NodeId = tTreeNesting.ChildKey 
					AND tTreeNesting.ParentId = @nNodeId
				INNER JOIN	tObjects ON tTree.ObjectId = tObjects.ObjectId
					AND (tObjects.ObjectType = N'PabloPicture' OR
							tObjects.ObjectType = N'PabloDocument' OR
	                        tObjects.ObjectType = N'PabloFile'))
	BEGIN
		SET @nReturn = -2
		RETURN
	END
	
	-- Kollar så man inte försöker ta bort Pabloobject som är kopplat i webbase 
/*BU
	IF EXISTS (SELECT     tObjectValue.ObjectId, tObjects.ObjectType
			FROM         tObjectValue 
			INNER JOIN	tObjects ON tObjectValue.ObjectId = tObjects.ObjectId 
				AND (tObjects.ObjectType = N'Picture' OR	tObjects.ObjectType = N'Document')
			WHERE     tObjectValue.Value = CAST((SELECT     ObjectId
						FROM         tTree
						WHERE     NodeId = @nNodeId) AS nvarchar(50)) )
	BEGIN
		SET @nReturn = -3
		RETURN
	END
*/

	-- Kollar så man inte försöker ta bort Pabloobject som är kopplat i webbase 
	
	SET @sObjectValue = CAST((SELECT  ObjectId
					FROM  tTree
					WHERE NodeId = @nNodeId) AS varchar(50)) 

	-- Finns som webbase-objekt?
	SET @nWBHits = (SELECT COUNT(DISTINCT(tObjectValue.ObjectId))
			FROM         tObjectValue 
			INNER JOIN	tObjects ON tObjectValue.ObjectId = tObjects.ObjectId 
				AND (tObjects.ObjectType = N'Picture' OR	tObjects.ObjectType = N'Document')
			WHERE     tObjectValue.Value = @sObjectValue)

	IF (@nWBHits > 0)

	BEGIN 
		--Finns någon i papperskorgen?
		SET @nRBHits = (SELECT  COUNT(DISTINCT(tObjectValue.ObjectId))
				FROM         tObjectValue 
				INNER JOIN	tObjects ON tObjectValue.ObjectId = tObjects.ObjectId 
					AND (tObjects.ObjectType = N'Picture' OR	tObjects.ObjectType = N'Document')
				INNER JOIN tTree ON tTree.ObjectId = tObjectValue.ObjectId
				WHERE     (tObjectValue.Value = @sObjectValue)
					AND  tTree.NodeId IN (SELECT ChildKey FROM tTreeNesting WHERE ParentId = dbo.GetRecycleBinId()))

		IF (@nRBHits > 0)

			BEGIN
				IF (@nWBHits = @nRBHits)	--alla träffar ligger i papperskorgen
					BEGIN
						SET @nReturn = -5
						RETURN
					END
				ELSE				--några av träffarna ligger i papperskorgen
					BEGIN
						SET @nReturn = -4
						RETURN
					END
			END
		ELSE						--inga träffar ligger i papperskorgen
			BEGIN
				SET @nReturn = -3
				RETURN
			END		

	END

	-- Kolla om det är Pablo-objekt som har kopplade tabeller
/*BU	IF EXISTS(SELECT o.ObjectId 
		FROM tObjects o
		INNER JOIN tTree t ON o.ObjectId=t.ObjectId AND t.NodeId = @nNodeId
		WHERE (o.ObjectType = N'PabloPicture' OR
							o.ObjectType = N'PabloDocument' OR
	                        o.ObjectType = N'PabloFile'))
	BEGIN

		SELECT @ObjectId = ObjectId FROM tTree WHERE NodeId = @nNodeId;
		SET @sObjectId = CAST(@ObjectId AS nvarchar(50))
		
		--SET @sSearchString = N'"type=PabloPicture ObjectId=' + @sObjectId + N'"'
		SET @sSearchString = N'"type=PabloPicture"'
		SET @sSearchString2 = N'"ObjectId=' + @sObjectId + N'"'

		IF EXISTS(SELECT ov.ObjectId
			FROM tObjectValue ov
			INNER JOIN tObjects o ON ov.ObjectId = o.ObjectId AND o.ObjectType = N'Table'
			INNER JOIN tSearchData sd ON ov.ObjectId = sd.ObjectId AND ov.LangId=sd.LangId
			WHERE CONTAINS(sd.AllSearchData, @sSearchString)
				AND CONTAINS(sd.AllSearchData, @sSearchString2))
		BEGIN
			SET @nReturn = -3
			RETURN
		END*/
		
	-- Kolla om det är Pablo-objekt som har kopplade tabeller
	IF EXISTS(SELECT o.ObjectId 
		FROM tObjects o
		INNER JOIN tTree t ON o.ObjectId=t.ObjectId AND t.NodeId = @nNodeId
		WHERE (o.ObjectType = N'PabloPicture' OR
							o.ObjectType = N'PabloDocument' OR
	                        o.ObjectType = N'PabloFile'))
	BEGIN

		SELECT @ObjectId = ObjectId FROM tTree WHERE NodeId = @nNodeId;
		SET @sObjectId = CAST(@ObjectId AS nvarchar(50))
		
		SET @sSearchString = N'"type=PabloPicture"'
		SET @sSearchString2 = N'"ObjectId=' + @sObjectId + N'"'

		--för att kunna göra koll om träffarna finns i papperskorg lagras värden i temptabell
		CREATE TABLE #TempHits (
			ObjectId int
		) 
	
		INSERT INTO #TempHits
			SELECT DISTINCT(ov.ObjectId)
				FROM tObjectValue ov
				INNER JOIN tObjects o ON ov.ObjectId = o.ObjectId AND o.ObjectType = N'Table'
				INNER JOIN tSearchData sd ON ov.ObjectId = sd.ObjectId AND ov.LangId=sd.LangId
				WHERE CONTAINS(sd.AllSearchData, @sSearchString)
					AND CONTAINS(sd.AllSearchData, @sSearchString2)

		SET @nWBHits = (SELECT COUNT(ObjectId) FROM #TempHits)

		IF @nWBHits > 0

		BEGIN

			SET @nRBHits = (SELECT  COUNT(#TempHits.ObjectId)
					FROM         #TempHits 
					INNER JOIN tTree ON tTree.ObjectId = #TempHits.ObjectId
					WHERE   tTree.NodeId IN 
						(SELECT ChildKey FROM tTreeNesting WHERE ParentId = dbo.GetRecycleBinId()))
			
			IF (@nRBHits > 0)	
				BEGIN
					IF (@nWBHits = @nRBHits)		
						SET @nReturn = -5	--alla träffar ligger i papperskorgen	
					ELSE				
						SET @nReturn = -4	--några av träffarna ligger i papperskorgen
				END
			ELSE						
				SET @nReturn = -3			--inga träffar ligger i papperskorgen
		END

		DROP TABLE #TempHits
		RETURN

	END


	-- Checking against the sysprop to see if we have RemoveObjectsWithShortcustOnRemoval set to false. 
	-- If so, then check to see if we have any shortcuts tied to this node
	IF EXISTS (SELECT [Value] FROM tSysProps WHERE [Name] = 'RemoveObjectsWithShortcustOnRemoval' AND [Value] = 0)
		BEGIN
			IF EXISTS (
				SELECT NodeId FROM tTree WHERE ShortcutTo IN (
						SELECT NodeId FROM tTree WHERE NodeId IN (
							SELECT ChildKey FROM tTreeNesting WHERE ParentId = @nNodeId
						)
				)
			)
			BEGIN
				SET @nReturn = -6	--We have shortcuts that needs to be deleted. 
			END
		END

/*
		--SET @sSearchString = N'"&FileName=' + @sObjectId + N'&"'

		IF EXISTS(SELECT ov.ObjectId
			FROM tObjectValue ov
			INNER JOIN tObjects o ON ov.ObjectId = o.ObjectId AND o.ObjectType = N'Table'
			INNER JOIN tSearchData sd ON ov.ObjectId = sd.ObjectId AND ov.LangId=sd.LangId
			WHERE CONTAINS(sd.AllSearchData, '"DownloadService.aspx?Type=StreamImage"')
				AND CONTAINS(sd.AllSearchData, @sSearchString))
		BEGIN
			SET @nReturn = -3
			RETURN
		END
*/

--	END





/** backup: from _MoveToRecycleBin 
	IF EXISTS (SELECT ObjectId 
		FROM tObjectValue 
		WHERE Value = Cast((Select ObjectId FROM tTree WHERE NodeId = @nNodeId) as nvarchar(50)))
	BEGIN
		SET @nReturn = -3
		RETURN
	END
**/

	RETURN @nReturn

Brian Donahue

You have to add the table or view to full-texct indexing. Unfortunately SQL Compare cannot check to make sure that this has been done so if you have an sp specifying a full-text query on a table that isn't full-text indexed, you will get an error.

You have to enable full-text indexing for the server and database and add each column using the sp_fulltext_column or sp_fulltext_table stored procedure.

cja100

Brian Donahue wrote:

You have to add the table or view to full-texct indexing. Unfortunately SQL Compare cannot check to make sure that this has been done so if you have an sp specifying a full-text query on a table that isn't full-text indexed, you will get an error.

You have to enable full-text indexing for the server and database and add each column using the sp_fulltext_column or sp_fulltext_table stored procedure.

I did that and its now working, thanks.