Question

我正在测试把我们的数据库从2005年服务器移至2008年。我们用CTE计算。

在使用全文时,国家情报和安全局不会出现错误。

在此,我不工作守则

WITH results  AS (  
 SELECT ROW_NUMBER() over (ORDER BY  GBU.CreateDate DESC ) as rowNum,             
 GBU.UserID,  
 NULL AS DistanceInMiles   
 FROM User GBU WITH (NOLOCK)  
 WHERE 1=1   
 AND GBU.CountryCode IN (SELECT [Value] FROM fn_Split( USA , , ))   
 AND GBU.UserID IN (SELECT [KEY] FROM CONTAINSTABLE(VW_GBU_Search, *,  COMPASS ))  
 )
SELECT * from results  
WHERE rowNum BETWEEN 0 and 25

如果我对CONTAINSTABLE线发表评论,则发言就执行。如果我只发言(没有发言),发言将罚款。

我发现的无益错误是:

Msg 0, Level 11, State 0, Line 0 A severe error occurred on the current command. The results, if any, should be discarded. Msg 0, Level 20, State 0, Line 0 A severe error occurred on the current command. The results, if any, should be discarded.

任何建议?

Answer 1

胜诉。见http://link.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=426981 。

象固定装置这样的声音应当放在下届中高分辨率高分辨率高分辨率高分辨率中。

Answer 2

假设其他答案是正确的,而根本问题是ug,因为你将拉卡纳克从CONTAINSTABLE中引出,或许像以下一点的疑问是:“ID”是VW_GBU_Search(未测试)中的ID栏?

;WITH results AS (  
 SELECT ROW_NUMBER() OVER (ORDER BY  GBU.CreateDate DESC ) AS rowNum,                     
   GBU.UserID,  
   NULL AS DistanceInMiles   
   FROM User GBU WITH (NOLOCK)  
   WHERE 1=1   
   AND GBU.CountryCode IN (SELECT [Value] FROM fn_Split( USA , , ))   
   AND GBU.UserID IN (SELECT ID FROM VW_GBU_Search WHERE CONTAINS(*,  COMPASS )) 
)
SELECT * FROM results  
  WHERE rowNum BETWEEN 0 AND 25

另外,你为什么有“1=1”条款? 您能否消除这一障碍?

Answer 3

我禁止我头几个小时反对隔离墙。

ASSUME: A table in database called 
        Items ( ItemId int PK, Content varchar(MAX) ), 
        which has a fulltext index already applied.

GO
CREATE FUNCTION udf_SearchItemsTable(@FreeText)
RETURNS @SearchHits
TABLE(
   Relevance int,
   ItemId int,
   Content varchar(MAX)
)
AS 
BEGIN
   INSERT @SearchHits
   SELECT Results.[Rank] AS Relevance
         ,Items.ItemId AS ItemId
         ,Items.Content AS Content
   FROM SearchableItems AS Items INNER JOIN 
         CONTAINSTABLE(SearchableItems, *, @FreeText) AS Results
           Results.[Key] = Items.Id
   RETURN
END
GO
...
GO
CREATE FUNCTION udf_SearchItems( @SearchText, @StartRowNum, @MaxRows)
RETURNS @SortedItems
TABLE (
   ItemId int,
   Content varchar(MAX)
)
AS
BEGIN
    WITH Matches AS
    (
         SELECT 
            ROW_NUMBER() OVER (ORDER BY Hits.Relevance DESC) AS RowNum
           ,Hits.*
        FROM ( udf_SearchItemsTable(@SearchText) ) AS Hits
    )
    SELECT
        ItemId, Content
    FROM
        Matches
    WHERE
        Matches.RowNum BETWEEN @StartRowNum 
    AND @StartRowNum + @MaxRows
    ;
    RETURN
END
GO   


select * from udf_SearchItems( some free text stuff , 10, 20)

友情链接