field in an embedded sql

I added full text indexing on the title field in a table but when i reference
the same field in an embedded sql it says cannot use contain on a field that
is not full text indexed.
example
SELECT top 400 s.story_id, u.title, s.title AS story_name, u.state,
CONVERT(char(10), u.air_date, 101) AS rundown_date,
'' AS video, '' AS cg_text, SUBSTRING(s.text, 1,500) AS script, SUBSTRING(i.
text, 1, 500) AS item_text, i.type, i.content_status, k.keyword, i.
editorial_description AS description, d.description AS notes,
s.editor AS creator, i.original_material_id AS clipname, i.ar_material_id AS
material_id
FROM
(
SELECT NULL AS state, NULL AS type, p.rundown_id, p.ncs_rundown_id, p.
edit_duration, p.title,
CONVERT(char(10), p.air_date, 101) AS air_date, SUBSTRING(CONVERT(varchar(10),
p.edit_start_time, 114), 1, 8) AS edit_start_time
FROM dbo.na_rundown_tbl p
WHERE (rundown_id NOT IN (SELECT ref1 FROM req_state_tbl WHERE (type = 401)))
) AS u
INNER JOIN dbo.na_story_tbl AS s ON s.rundown_id = u.rundown_id
LEFT OUTER JOIN dbo.na_item_tbl AS i ON s.story_id = i.story_id
LEFT OUTER JOIN dbo.na_itemkeyword_tbl AS k ON i.item_id = k.item_id
LEFT OUTER JOIN dbo.na_itemdesc_tbl AS d ON i.item_id = d.item_id where
contains (u.title ,'%midlothian%')
error message received:
Msg 7601, Level 16, State 3, Line 1
Cannot use a CONTAINS or FREETEXT predicate on column 'title' because it is
not full-text indexed.
what does sp_help_fulltext_tables 'catalogname','title' return?
make sure you replace catalogname with the name of your catalog.
Hilary Cotter
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
"ARPREET" <u31535@.uwe> wrote in message news:6d68cc930bce5@.uwe...
>I added full text indexing on the title field in a table but when i
>reference
> the same field in an embedded sql it says cannot use contain on a field
> that
> is not full text indexed.
> example
> SELECT top 400 s.story_id, u.title, s.title AS story_name, u.state,
> CONVERT(char(10), u.air_date, 101) AS rundown_date,
> '' AS video, '' AS cg_text, SUBSTRING(s.text, 1,500) AS script,
> SUBSTRING(i.
> text, 1, 500) AS item_text, i.type, i.content_status, k.keyword, i.
> editorial_description AS description, d.description AS notes,
> s.editor AS creator, i.original_material_id AS clipname, i.ar_material_id
> AS
> material_id
> FROM
> (
> SELECT NULL AS state, NULL AS type, p.rundown_id, p.ncs_rundown_id, p.
> edit_duration, p.title,
> CONVERT(char(10), p.air_date, 101) AS air_date,
> SUBSTRING(CONVERT(varchar(10),
> p.edit_start_time, 114), 1, 8) AS edit_start_time
> FROM dbo.na_rundown_tbl p
> WHERE (rundown_id NOT IN (SELECT ref1 FROM req_state_tbl WHERE (type =
> 401)))
> ) AS u
> INNER JOIN dbo.na_story_tbl AS s ON s.rundown_id = u.rundown_id
> LEFT OUTER JOIN dbo.na_item_tbl AS i ON s.story_id = i.story_id
> LEFT OUTER JOIN dbo.na_itemkeyword_tbl AS k ON i.item_id = k.item_id
> LEFT OUTER JOIN dbo.na_itemdesc_tbl AS d ON i.item_id = d.item_id where
> contains (u.title ,'%midlothian%')
> error message received:
> Msg 7601, Level 16, State 3, Line 1
> Cannot use a CONTAINS or FREETEXT predicate on column 'title' because it
> is
> not full-text indexed.
>
|||title is a field name . I type sp_help_fulltext_tables 'catalogname',
'tablename' it returns one row
I had created the index using the below script
create fulltext catalog cat1
create unique index ui_rundown_tbl on na_rundown_tbl (rundown_id)
create fulltext index on na_rundown_tbl (title)
key index ui_rundown_tbl on cat1 with change_tracking auto
Hilary Cotter wrote:[vbcol=seagreen]
>what does sp_help_fulltext_tables 'catalogname','title' return?
>make sure you replace catalogname with the name of your catalog.
>[quoted text clipped - 34 lines]
|||Hello ARPREET,
Move your contains inside the derived table.
u resolves to a derived table and not the underlying table na_rundown_tbl.
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons

> SELECT top 400 s.story_id, u.title, s.title AS story_name, u.state,
> CONVERT(char(10), u.air_date, 101) AS rundown_date,
> '' AS video, '' AS cg_text, SUBSTRING(s.text, 1,500) AS script,
> SUBSTRING(i.
> text, 1, 500) AS item_text, i.type, i.content_status, k.keyword, i.
> editorial_description AS description, d.description AS notes,
> s.editor AS creator, i.original_material_id AS clipname,
> i.ar_material_id AS
> material_id
> FROM
> (
> SELECT NULL AS state, NULL AS type, p.rundown_id, p.ncs_rundown_id, p.
> edit_duration, p.title,
> CONVERT(char(10), p.air_date, 101) AS air_date,
> SUBSTRING(CONVERT(varchar(10),
> p.edit_start_time, 114), 1, 8) AS edit_start_time
> FROM dbo.na_rundown_tbl p
> WHERE (rundown_id NOT IN (SELECT ref1 FROM req_state_tbl WHERE (type =
> 401)))
> ) AS u
> INNER JOIN dbo.na_story_tbl AS s ON s.rundown_id = u.rundown_id
> LEFT OUTER JOIN dbo.na_item_tbl AS i ON s.story_id = i.story_id
> LEFT OUTER JOIN dbo.na_itemkeyword_tbl AS k ON i.item_id = k.item_id
> LEFT OUTER JOIN dbo.na_itemdesc_tbl AS d ON i.item_id = d.item_id
> where
> contains (u.title ,'%midlothian%')