Full Text Commands in SQL Query?

My host does allow me to create full text catalogs. I use SQL Server Management Studio Express, so I'd have to do it "by hand" as opposed to point-clicking menus and going through wizards. Where do I go to find those commands? I've seen them before but I can't remember where to get them. They're the ones that begin with sp_.

I need the commands for:

Creating a full text catalog

Specifying which columns and tables to enable full text searching

Scheduling when to update the catalog (when to repopulate, etc.)

Clearing the catalog to start over again

Any other handy/necessary commands?

Any help will be appreciated. Thank you.

i sent you that info here:http://forums.asp.net/t/1127286.aspx

|||

i sent you that info here:http://forums.asp.net/t/1127286.aspx

|||

That's creating the catalog and enabling table/column for full-text. I got those.

What about for scheduling population?

|||

1. Activate your database for full-text search:
sp_fulltext_database 'enable'
(Warning! An already existing index will be reset to the default settings.)

2. Create the full text index: To continue, a so-called catalog must be created:
sp_fulltext_catalog 'Fulltextcatalog1','create'
('Fulltextcatalog1' is the name of the catalog for this example)

and a virtual index:
sp_fulltext_table 'Employee','create','Fulltextcatalog1','pkID_Field'
(pkID_Field is the name of the Primary Key of table Employee.)

3. Add the fields that you want to be indexed:
(The fields have to be either the type char, varchar, nchar, nvarchar or text.)
sp_fulltext_column 'Employee','lastname','add'
sp_fulltext_column 'Employee','firstname','add'

where 'lastname' and 'firstname' are the field names used in this example.

4. Next you have to activate the index:
sp_fulltext_table 'Employee','activate'

5. After that the index can be generated with the following command:
sp_fulltext_table 'Employee','start_full'

6. Now activate the 'Change Tracking'. Required for this, is a field with the data
type 'timestamp'. Through this, the newly created index will automatically be
updated, as soon as the data in the columns has been changed.

sp_fulltext_table Employee, 'Start_change_tracking'
sp_fulltext_table Employee, 'Start_background_updateindex'

Deleting Full-text Indexing on a table named fulltext1

1. First delete the index:
sp_fulltext_table 'Employee', 'drop'

2. And then the catalog:
sp_fulltext_catalog 'fulltextcatalog1','drop'

Full Tex Search question...

I am trying to get a Full Text Search function together for a new application, but don't know the Full Text commands too well.

Does anyone happen to know if it is possible to query a Full Text Indexed table without using dynamic SQL? Here is the example

create table test1
(col1 varchar(10),
col2 varchar(100))
-- You have to do the full text indexing yourself here...
insert into test1
select 'good row', 'here is some talk about supplies'
union
select 'good row', 'this is what we supplied'
union
select 'good row', 'supply and demand. Live it. Love it.'
union
select 'bad row', 'This is a row that should be ignored'
go
-- Don't forget to update the full text index...
declare @.word varchar(20)
declare @.query varchar(500)

set @.word = 'supply'

set @.query = 'select * from test1 where contains (Col2, ''formsof (inflectional, "' + @.word + '") or "*' + @.word + '*"'')'

print @.query

exec (@.query)

I can do the simple queries with

where contains (col2, "@.word")

But the FORMSOF part breaks down, and will not accept variables from me. Am I doomed to Dynamic SQL? Has anyone found better ways of doing this? Thanks in advance.This works for me - try it.

drop table ftable
go
create table ftable(id int identity primary key, code varchar(25))
go
insert ftable(code) values('table')
insert ftable(code) values('tables')
insert ftable(code) values('tables table')
insert ftable(code) values('tables able')
go
-- do what it needs to do for creating a full-text index
declare @.word varchar(20)
declare @.query varchar(500)
set @.word = 'table'
set @.query = 'select * from ftable where contains (*, ''formsof (inflectional, "' + @.word + '") or "*' + @.word + '*"'')'
print @.query
exec (@.query)|||Thanks, Snail. I have gotten about that far, too. I was hoping for a non-Dynamic SQL way out. Unfortunately, I do not think you can supply paramters directly to a FORMSOF clause.|||OK, Now I am ticked at Microsoft.

I put in the stored procedure with the Dynamic SQL, but I forgot one important "feature" of dynamic SQL. The users need permissions on the tables AND the stored proc in order to run it. No problem, I say. I can protect the tables with a view. After all, that IS what Microsoft says in ALL of their documentation, right?

Turns out, you can not do a Full Text Query on a view of a Full Text Indexed column..

Server: Msg 7601, Level 16, State 2, Line 1
Cannot use a CONTAINS or FREETEXT predicate on table 'DatabaseSearchResults' because it is not full-text indexed.

So, has Microsoft turned their backs on "Best Practices"?

Alternatively, how do the rest of you deal with Full Text Indexing? Any pointers?