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
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.