Hi,
I'm putting together a system and one of the requirements is to have a
searchable CV function.
I've got all the code to load the files on to the image fields, I've indexed
and got it mostly working.
Before I go to far down the road what is your opinion on having txt files
instead of doc files to search? The SQL seems to be more flexible on
searches rather than on the binary files and the index files themselves are
smaller.
My main concern is I'd have to do the text conversion automatically, any
pointers on this?
Does anyone have any views on the best way to go about this
Many thanks for any help you can give
Jim FlorenceHave you tried asking this in the sqlserver.fulltext news group?
As long as all expected binary types are supported by appropriate IFilters
on your server, using binary data will be just fine - AFAIK.
ML
http://milambda.blogspot.com/|||Thanks for the pointer, embarrassingly I didn't see it existed :0/
I just noticed on trying one query with "select like" it would only work on
varchar fields and failed on the blob, could well be mistaken though.
Thanks again
Jim
"ML" <ML@.discussions.microsoft.com> wrote in message
news:45DBD971-2EE7-42C5-AD3A-C3F542B4F8DA@.microsoft.com...
> Have you tried asking this in the sqlserver.fulltext news group?
> As long as all expected binary types are supported by appropriate IFilters
> on your server, using binary data will be just fine - AFAIK.
>
> ML
> --
> http://milambda.blogspot.com/|||The LIKE operatoron binary values works if the searched string is cast to
binary or varbinary, but only if the binary column contains a true
representation of the text, which is not how Word documents are represented.
What exactly did you try?
BTW: with FTI you really shouldn't use the LIKE operator - FTS functions are
more efficient in this case.
ML
http://milambda.blogspot.com/|||Thaks for thatr I knew I'd have missed something obvious.
I'm not entierly sure what the difference is between FTI and FTS, I thought
they were one and the same
Jim
"ML" <ML@.discussions.microsoft.com> wrote in message
news:29D91E26-BE4C-4832-B888-8FA47423C793@.microsoft.com...
> The LIKE operatoron binary values works if the searched string is cast to
> binary or varbinary, but only if the binary column contains a true
> representation of the text, which is not how Word documents are
> represented.
> What exactly did you try?
> BTW: with FTI you really shouldn't use the LIKE operator - FTS functions
> are
> more efficient in this case.
>
> ML
> --
> http://milambda.blogspot.com/|||Jim,
I am trying to do something similar. I have a list of .doc files that I am
trying to do i full text search on. I would really appreciate some help on
how to do this. Some sql code would be great. Thanks
qtrsteve@.yahoo.com|||Sorry for a late reply.
FTS = full-text search
FTI = full-text index
FTS uses FTI.
I was referring to FTS functions: contains, containstable, frettext,
freetexttable. FTS functions are far more efficient than the LIKE operator,
or at least the processing overhead of FTS is pretty constant compared to th
e
overhead when using the LIKE operator.
ML
http://milambda.blogspot.com/sql
2012年3月27日星期二
2012年3月26日星期一
full text search on doc files
I have a list of word files (.doc) I am trying to us the full text
search in sql server to find key words in these word documents. I have
a Windows XP and sql server installed on my machine. ( no network
server). I cant seem to get the code to work to do a search on these
files. I would really appreciate if someone can send me some code from
start to finsh so I can create the sql tables and then catolog to
search for keywords in the word files.What version of SQL Server are you using? Word documents must be stored in
columns of data type image, and another column must be present to store the
type suffix (".doc" for MS Word files).
When full-text indexing is activated for an image column the name of the
column where the suffix is stored must also be set. How this is actually don
e
depends on the SQL Server version.
SQL 2000:
sp_fulltext_column
@.tabname = '<qualified_table_name>'
,@.colname = '<column_name>'
,@.action = 'add'
,@.type_colname = '<type_column_name>'
SQL 2005:
create fulltext index on <table_name>
(<column_name> TYPE COLUMN <type_column_name>
key index <index_name>
If the index has been created appropriately, then you might also check the
SQL Error Log:
http://milambda.blogspot.com/2005/1...n-or-bybug.html
ML
http://milambda.blogspot.com/|||ML,
How do i create the table first and get the files indexed? How do I
get the files catologued? i am using sql2000 . Do i run the sql you
wrote above after the files have been imported?|||Setting up full-text indexing in SQL 2000 is very well documented in Books
Online:
http://msdn.microsoft.com/library/d... />
d_6g1f.asp
After you've created the table and enabled the database for full-text
search, you need to enable the table, then the column for full-text indexing
.
It really doesn't matter whether you set up FTI before inserting data in the
tables, or after, as long as you propagate change to the catalogue before yo
u
try to use full-text search (new rows will not be found by the engine until
the contents have been indexed).
There are several methods of propagating change to the full-text catalogue:
full propagation, incremental propagation and background propagation (with
change tracking). With background propagation new rows are indexed after
they've been inserted and changed rows are indexed after they've been
updated, but there's a performance impact and writetext/updatetext operation
s
are not detected.
To avoid the downside you could use incremental propagation by creating a
SQL Agent Job that starts incremental propagation as frequently as you need.
Are you having any specific problems? You could also ask these questions in
the dedicated newsgorup: "microsoft.public.sqlserver.fulltext".
ML
http://milambda.blogspot.com/|||ML,
I have looked through the link, still not getting it to work. I guess
i need some code, from start to finish, loading the file from the
c:\... and then running the sql. Anything you may have would be
extremely helpful..thanks|||The link I sent you will help you set up full-text indexing, but the actual
method of inserting MS Word documents into the table depends on your client
application.
If you need to insert blobs through T-SQL, you could use BULK INSERT:
http://msdn.microsoft.com/library/d...br />
4fec.asp
Also make sure you create the format file in accordance to this KB article:
http://support.microsoft.com/defaul...kb;en-us;271344
Information on using format files:
http://msdn.microsoft.com/library/d...>
bcp_9yat.asp
ML
http://milambda.blogspot.com/
search in sql server to find key words in these word documents. I have
a Windows XP and sql server installed on my machine. ( no network
server). I cant seem to get the code to work to do a search on these
files. I would really appreciate if someone can send me some code from
start to finsh so I can create the sql tables and then catolog to
search for keywords in the word files.What version of SQL Server are you using? Word documents must be stored in
columns of data type image, and another column must be present to store the
type suffix (".doc" for MS Word files).
When full-text indexing is activated for an image column the name of the
column where the suffix is stored must also be set. How this is actually don
e
depends on the SQL Server version.
SQL 2000:
sp_fulltext_column
@.tabname = '<qualified_table_name>'
,@.colname = '<column_name>'
,@.action = 'add'
,@.type_colname = '<type_column_name>'
SQL 2005:
create fulltext index on <table_name>
(<column_name> TYPE COLUMN <type_column_name>
key index <index_name>
If the index has been created appropriately, then you might also check the
SQL Error Log:
http://milambda.blogspot.com/2005/1...n-or-bybug.html
ML
http://milambda.blogspot.com/|||ML,
How do i create the table first and get the files indexed? How do I
get the files catologued? i am using sql2000 . Do i run the sql you
wrote above after the files have been imported?|||Setting up full-text indexing in SQL 2000 is very well documented in Books
Online:
http://msdn.microsoft.com/library/d... />
d_6g1f.asp
After you've created the table and enabled the database for full-text
search, you need to enable the table, then the column for full-text indexing
.
It really doesn't matter whether you set up FTI before inserting data in the
tables, or after, as long as you propagate change to the catalogue before yo
u
try to use full-text search (new rows will not be found by the engine until
the contents have been indexed).
There are several methods of propagating change to the full-text catalogue:
full propagation, incremental propagation and background propagation (with
change tracking). With background propagation new rows are indexed after
they've been inserted and changed rows are indexed after they've been
updated, but there's a performance impact and writetext/updatetext operation
s
are not detected.
To avoid the downside you could use incremental propagation by creating a
SQL Agent Job that starts incremental propagation as frequently as you need.
Are you having any specific problems? You could also ask these questions in
the dedicated newsgorup: "microsoft.public.sqlserver.fulltext".
ML
http://milambda.blogspot.com/|||ML,
I have looked through the link, still not getting it to work. I guess
i need some code, from start to finish, loading the file from the
c:\... and then running the sql. Anything you may have would be
extremely helpful..thanks|||The link I sent you will help you set up full-text indexing, but the actual
method of inserting MS Word documents into the table depends on your client
application.
If you need to insert blobs through T-SQL, you could use BULK INSERT:
http://msdn.microsoft.com/library/d...br />
4fec.asp
Also make sure you create the format file in accordance to this KB article:
http://support.microsoft.com/defaul...kb;en-us;271344
Information on using format files:
http://msdn.microsoft.com/library/d...>
bcp_9yat.asp
ML
http://milambda.blogspot.com/
2012年2月19日星期日
FTS Cluster Setup
I have an existing SQL 2000 Cluster setup and would like to add Full
Text search to it. Is there any specific doc or best practices for
this configuration? I have started the install once but the only
option was to uninstall SQL Server. Do I run ftsetup.exe instead?
From what I recall, SQL FTS is part of a cluster install. There is no option
to deselect it.
Are you having a problem with it?
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
"AF" <af.at.work@.gmail.com> wrote in message
news:1170700421.768499.282270@.h3g2000cwc.googlegro ups.com...
>I have an existing SQL 2000 Cluster setup and would like to add Full
> Text search to it. Is there any specific doc or best practices for
> this configuration? I have started the install once but the only
> option was to uninstall SQL Server. Do I run ftsetup.exe instead?
>
|||On Feb 6, 11:51 am, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:[vbcol=seagreen]
> From what I recall, SQL FTS is part of a cluster install. There is no option
> to deselect it.
> Are you having a problem with it?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTShttp://www.indexserverfaq.com
> "AF" <af.at.w...@.gmail.com> wrote in message
> news:1170700421.768499.282270@.h3g2000cwc.googlegro ups.com...
It wasn't showing up in EM - after restarting the service it did
appear. Thanks.
Text search to it. Is there any specific doc or best practices for
this configuration? I have started the install once but the only
option was to uninstall SQL Server. Do I run ftsetup.exe instead?
From what I recall, SQL FTS is part of a cluster install. There is no option
to deselect it.
Are you having a problem with it?
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
"AF" <af.at.work@.gmail.com> wrote in message
news:1170700421.768499.282270@.h3g2000cwc.googlegro ups.com...
>I have an existing SQL 2000 Cluster setup and would like to add Full
> Text search to it. Is there any specific doc or best practices for
> this configuration? I have started the install once but the only
> option was to uninstall SQL Server. Do I run ftsetup.exe instead?
>
|||On Feb 6, 11:51 am, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:[vbcol=seagreen]
> From what I recall, SQL FTS is part of a cluster install. There is no option
> to deselect it.
> Are you having a problem with it?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTShttp://www.indexserverfaq.com
> "AF" <af.at.w...@.gmail.com> wrote in message
> news:1170700421.768499.282270@.h3g2000cwc.googlegro ups.com...
It wasn't showing up in EM - after restarting the service it did
appear. Thanks.
订阅：
博文 (Atom)