Severe error when trying to FREETEXTTABLE an indexed view with a CTE

Question

Hi,

Where stockView is an indexed view with a full-text index, I receive the error message below. The database is running on a 2008 Express engine in 2005 compatibility mode.

Code:

with stockCte (title, grade, price, weighted)
as
(
    select sv.[title]     ,
      sv.[grade]     ,
      sv.[price]     ,
      (case when sv.[issue] = @issue and svs.[rank] > 30
       then svs.[rank] + 100
       else svs.[rank]
       end)     weighted
    from stockView sv
    inner join freetexttable(stockView, (name), @term) svs
     on sv.[id] = svs.[key]
)
select * from stockCte;

Error:

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.

The query works when I remove the inner join and the weighted column. Any ideas, I'm at a loss.

Answer 1

Error Level 11 is database object not found; does the select on the freetexttable query work as a select ? If so does the full query work as a select (without the cte definition?)

Answer 2

Reluctantly I have resorted to using a table variable instead of a CTE.

declare @stockTemp table(
    title    nvarchar(100),
    grade    nvarchar(50),
    price    money,
    row     bigint
);

insert into @stockTemp
select sv.[title]       ,
     sv.[grade]     ,
     sv.[price]     ,
     row_number() over (order by (case when sv.[issue] = @issue and svs.[rank] > 30
              then svs.[rank] + 100
              else svs.[rank]
              end) desc,
             sv.title,
             sv.grade desc,
             sv.price asc)
from stockView sv
inner join freetexttable(stockView, (*), @term) svs
    on sv.[id] = svs.[key]

select * from @stockTemp;

If anyone has any better suggestions, please let me know.