March 25, 2009 at 4:55 am
Hi all.
I am quite new to SQL and I have a problem :
I am trying to use a full text search function (contains) on a view, but it raises me some problems.
I have some tables included in a view. One of them, Cause, has a text column with a full text index on it.
So I am able to do a search like that :
select * from Cause where contains(Nom_Cause, '"malaise"')
The problem is when I try to do that on a view that include this table.
When I make this sql request :
select * from vue_evenements where contains(Nom_Cause, '"malaise"')
the sql engine says me that it is not allowed because there is no full text index (Msg 7601).
But, when I call the "contains" directly on the SQL request of the view, it works :
SELECT dbo.Evenement.Numero_Evenement AS [N° Evt],dbo.Cause.Nom_Cause AS Cause
FROM dbo.Evenement LEFT OUTER JOIN dbo.Cause ON dbo.Evenement.Numero_Cause = dbo.Cause.Numero_Cause
where contains(Nom_Cause, '"malaise"')
It seems that, in order to use a FTS on a view, I will have to create a clustered index on that view. But in my case, I can't do it because I have outer joins in the view.
Is there a way to call a FTS search on a view without creating an index on it? Why is it necessary whereas calling the SQL view request with the FTS search works perfectly?
Gilles.
March 26, 2009 at 9:40 am
Sorry, it is not supported. Even if the view is a 1:1 match on the table (i.e., no joins) it does not work. The problem is due to a stupid error in SQL Server where it cannot resolve the view down to the base table when checking for full-text.
BTW, Oracle can handle full-text searches on columns in views.
This is also a major security problem as you must grant SELECT on the base table to the executing user.
March 27, 2009 at 8:22 am
Snif Snif 🙁
Thanks for the reply.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply