create store procedure to incremental populate full text index tab

I need to create a store procedure to perform incremental population of full
text index table. The store procedure will be call from my program.
Any ideas ?
xxx
This is probably what you want.
exec sp_fulltext_table N'[dbo].[authors]', N'start_incremental'
I suggest you have a look at change tracking and schedule the index updates.
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
"eslim" <eslim@.discussions.microsoft.com> wrote in message
news:AEAD8DB8-91F6-484A-AD2C-C6AB06E5B38B@.microsoft.com...
>I need to create a store procedure to perform incremental population of
>full
> text index table. The store procedure will be call from my program.
> Any ideas ?
> --
> xxx

CREATE FULLTEXT CATALOG x ON FILEGROUP 'PRIMARY' issue

Books online recommends creating the FTS catalog in a new filegroup
I have created a FileGroup called FTS (not readonly and not default)
However the following syntax fails.
CREATE FULLTEXT CATALOG Z_Search_Freetext
ON FILEGROUP 'FTS'
IN PATH 'C:\Program Files\Microsoft SQL Server\MSSQL\FTDATA\' -- TODO
'f:\MSSQL\FDATA\'
WITH ACCENT_SENSITIVITY = ON
AUTHORIZATION dbo
with
Incorrect syntax near 'FTS'.
If I remove ON FILEGROUP 'FTS' the following works without error
CREATE FULLTEXT CATALOG Z_Search_Freetext
IN PATH 'C:\Program Files\Microsoft SQL Server\MSSQL\FTDATA\' -- TODO
'f:\MSSQL\FDATA\'
WITH ACCENT_SENSITIVITY = ON
AUTHORIZATION dbo
Any assistance would be appreciated.Looks like I forgot to add a file to the filegroup. DOH!
"Richard Yeo" wrote:

> Books online recommends creating the FTS catalog in a new filegroup
> I have created a FileGroup called FTS (not readonly and not default)
> However the following syntax fails.
> CREATE FULLTEXT CATALOG Z_Search_Freetext
> ON FILEGROUP 'FTS'
> IN PATH 'C:\Program Files\Microsoft SQL Server\MSSQL\FTDATA' -- TODO
> 'f:\MSSQL\FDATA'
> WITH ACCENT_SENSITIVITY = ON
> AUTHORIZATION dbo
> with
> Incorrect syntax near 'FTS'.
> If I remove ON FILEGROUP 'FTS' the following works without error
> CREATE FULLTEXT CATALOG Z_Search_Freetext
> IN PATH 'C:\Program Files\Microsoft SQL Server\MSSQL\FTDATA' -- TODO
> 'f:\MSSQL\FDATA'
> WITH ACCENT_SENSITIVITY = ON
> AUTHORIZATION dbo
> Any assistance would be appreciated.

CREATE FULLTEXT CATALOG x ON FILEGROUP 'PRIMARY' issue

Books online recommends creating the FTS catalog in a new filegroup
I have created a FileGroup called FTS (not readonly and not default)
However the following syntax fails.
CREATE FULLTEXT CATALOG Z_Search_Freetext
ON FILEGROUP 'FTS'
IN PATH 'C:\Program Files\Microsoft SQL Server\MSSQL\FTDATA\' -- TODO
'f:\MSSQL\FDATA\'
WITH ACCENT_SENSITIVITY = ON
AUTHORIZATION dbo
with
Incorrect syntax near 'FTS'.
If I remove ON FILEGROUP 'FTS' the following works without error
CREATE FULLTEXT CATALOG Z_Search_Freetext
IN PATH 'C:\Program Files\Microsoft SQL Server\MSSQL\FTDATA\' -- TODO
'f:\MSSQL\FDATA\'
WITH ACCENT_SENSITIVITY = ON
AUTHORIZATION dbo
Any assistance would be appreciated.Looks like I forgot to add a file to the filegroup. DOH!
"Richard Yeo" wrote:
> Books online recommends creating the FTS catalog in a new filegroup
> I have created a FileGroup called FTS (not readonly and not default)
> However the following syntax fails.
> CREATE FULLTEXT CATALOG Z_Search_Freetext
> ON FILEGROUP 'FTS'
> IN PATH 'C:\Program Files\Microsoft SQL Server\MSSQL\FTDATA\' -- TODO
> 'f:\MSSQL\FDATA\'
> WITH ACCENT_SENSITIVITY = ON
> AUTHORIZATION dbo
> with
> Incorrect syntax near 'FTS'.
> If I remove ON FILEGROUP 'FTS' the following works without error
> CREATE FULLTEXT CATALOG Z_Search_Freetext
> IN PATH 'C:\Program Files\Microsoft SQL Server\MSSQL\FTDATA\' -- TODO
> 'f:\MSSQL\FDATA\'
> WITH ACCENT_SENSITIVITY = ON
> AUTHORIZATION dbo
> Any assistance would be appreciated.

CREATE FULLTEXT CATALOG inside a user transaction.

hi:

I try to create full text for new created tables.

Since all new created tables will have same columns with different table name.

After I run the stored procedure to create table, after i got the new table name, I would like to create full-text on that table in the DDL triger.

But I got error like this:

CREATE FULLTEXT CATALOG statement cannot be used inside a user transaction.

Any one has idea how to deal with it?

Thanks

This is by design. Full-text catalog cannot be created in nested/embedded transactions.

I dont' think the DDL trigger would work in this case. Perhap, create a store proc that will scan all the tables that do not have full-text index and add them on fly.

Gary

|||

DDL triger not working. I have stored procedure for creating full-text, the SP works when you exec it inside of the Management studio (not being called from other SP, job or triger etc), otherwise, it will return the same error.

anyway, I guess this is the dead end for creating full text on the fly.

CREATE FTC Failing for

Trying to create a catalog as seen below:

CREATE FULLTEXT INDEX ON [dbo].[AttachFiles](

[BinFile])

KEY INDEX [PK_AttachFiles] ON [Dossiers_FTC]

WITH CHANGE_TRACKING OFF

GO

Getting error of:

Msg 7655, Level 16, State 1, Line 1

TYPE COLUMN option must be specified with column of image or varbinary(max) type.

BinFile is an Image datatype.

What do I need to update on my CREATE statement above to make this work?

When you use an IMAGE or VARBINARY(MAX) field for a full-text index, you have to tell the service how to read the binary image. It has a set of extensions installed with which it can read these. You can see the list by executing a "SELECT * FROM sys.fulltext_document_types" query in the full-text enabled database. To tell the service what image type is stored in the field, you need another field that holds the type name.

So, let's assume Dossiers_FTC holds MS Word Document files. Let's also assume you have a field in your table named "BinType" that contains the string ".doc". Your statement would look like this:

CREATE FULLTEXT INDEX ON [dbo].[AttachFiles]

([BinFile] TYPE COLUMN [BinType]) KEY INDEX [PK_AttachFiles] ON [Dossiers_FTC]

WITH CHANGE_TRACKING OFF

GO