View to a table with an FTI

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

MrTim - 27 Jul 2006 13:33 GMT

We recently created a table with an FTI in a database, then created a view to
this table in another database. We found we were not able to access the FTI
using CONTAINS through the view.

We got round it by changing our SP to use the FQN instead of the view, but
it was of interest that the view method didn't work. Feature/bug??

Reply to this Message

Simon Sabin - 28 Jul 2006 00:14 GMT

Hello MrTim,

Feature.

Imagine if the view referenced 2 tables with full text indexes, wouldn't
work.

Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons

> We recently created a table with an FTI in a database, then created a
> view to this table in another database. We found we were not able to
[quoted text clipped - 3 lines]
> but it was of interest that the view method didn't work.
> Feature/bug??

Reply to this Message

Hilary Cotter - 29 Jul 2006 11:41 GMT

I'm confused. I can get this to work. In pubs, FTI the au_lname column in
authors table, then in northwinds create this view

use northwind
GO
create view test
as
select * from pubs.dbo.authors where contains(*,'ringer')
GO
create view test1
as
select * from pubs.dbo.authors
join (select * from containstable(pubs.dbo.authors,*,'ringer')) as x
on x.[key]=pubs.dbo.authors.au_id
GO
select * from test
--two rows returned.
select * from test1
--two rows returned.

Signature

Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.

This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

> We recently created a table with an FTI in a database, then created a view
> to
[quoted text clipped - 4 lines]
> We got round it by changing our SP to use the FQN instead of the view, but
> it was of interest that the view method didn't work. Feature/bug??

Reply to this Message

Daniel Crichton - 31 Jul 2006 13:02 GMT

Hilary, I think he meant doing something like this:

create view test1 as
select * from pubs.dbo.authors
go

select * from test1 where contains(*,'ringer')

which won't work because the FTI is on the base table, not on the view.

Dan

> I'm confused. I can get this to work. In pubs, FTI the au_lname column in
> authors table, then in northwinds create this view
[quoted text clipped - 25 lines]
>> but
>> it was of interest that the view method didn't work. Feature/bug??

Reply to this Message

Hilary Cotter - 31 Jul 2006 13:57 GMT

Duh, ok in SQL 2005 you can fulltext index views which should allow this.

Signature

> Hilary, I think he meant doing something like this:
>
[quoted text clipped - 37 lines]
>>> but
>>> it was of interest that the view method didn't work. Feature/bug??

Reply to this Message