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

Question

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.

I don't see anything off the top of my head. Any particular reason you made this a CTE instead of just using the inner select? — Peter Oehlert, Jun 14 '09 at 16:56
The inner select was just me getting started, the eventual select will include paging functionality that will be exposed via the CTE. — kim3er, Jun 14 '09 at 17:22
If you see 'A severe error occurred on the current command' it means most likely the server hit an internal runtime assert or an access violation. It has produced a minidump located in your LOG folder (named sqldump____.mdmp). You can submit this dump to customer support and they can investigate and advise. — Remus Rusanu, Jun 15 '09 at 12:22

score 1 · Answer 1 · answered Jul 01 '11 at 00:38

1

It hasn't been fixed in R2 either, but there is a hotfix for it - see KB article #2421014.

answered Jul 01 '11 at 00:38

user735232

181
1
5

score 0 · Answer 2 · answered Jun 14 '09 at 17:06

0

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?)

answered Jun 14 '09 at 17:06

u07ch

12,217
5
40
47

The inner select works just fine without the CTE definition and the CTE definition works just fine when freettexttable references a table rather than the view. It must be the combination of a CTE and an indexed view. – kim3er Jun 14 '09 at 17:24

score 0 · Accepted Answer · answered Jun 14 '09 at 17:43

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.

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

3 Answers3