create site search using sql server "full text search"

would you use sql server "full text search" feature as your site index? from some reason i can't make index server my site search catalog, and i wonder if the full text is the solution. i think that i wll have to you create new table called some thing like "site text" and i will need to write every text twice- one the the table (let's say "articles table") and one to the text. other wise- there is problems finding the right urlof the text, searching different tables with different columns name and so on...

so i thought create site search table, with the columns:

id, text, url

and to write every thing to this table.

but some how ot look the wrong way, that every forum post, every article, album picture or joke will insert twice to the sqr server...

what do you think?

Full text search is handled by SQL Server itslef (via an external windows service) and is a good solution but you need to go through pros and cons and take care about when to generate the indexes and what columns to index. Refer these articles for details:

http://www.eggheadcafe.com/articles/20010422.asp

http://www.developer.com/db/article.php/3446891

Hope this helps,

Vivek

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