SQL Server 2008 CTE And CONTAINSTABLE Statement - Why the error?

Question

I am testing out moving our database from SQL Server 2005 to 2008. We use CTE's for paging.

When using full-text CONTAINSTABLE, the CTE will not run and generates an error.

Here's my non-working code-

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

If I comment out the CONTAINSTABLE line, the statement executes. If I only run the SELECT statement (not the WITH), the statement executes fine.

The un-helpful error I get on this is:

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.

Any suggestions?

Answer 1

Appears to be a bug. See http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=426981

Sounds like the fix should be in the next MSSQL SP.

Answer 2

Assuming the other answers are correct, and that the underlying issue is a bug, since you aren't referencing RANK from CONTAINSTABLE, perhaps a query something like the following would be a workaround, where "ID" is the ID column in VW_GBU_Search (untested)?

;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

Also, why do you have the "1=1" clause? Can you eliminate it?

Answer 3

i banged my head against the wall on this problem for hours, 
here is a workaround that works

    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)