sql server full text search:
Hi all,

this is my first time I am posting a question here and hope someone will
make some minds thoughts about it. BTW I am a newbie in SQL topic.



What I want to achive is for someone here maybe simple, so I would be
happy if I could get a step-by step explanation, cause no existing docs
on the web and BOL help me.



And here the task. (very simplified)



I want to populate a table TABLE1 colums(how many are required?)
whereas one of them(STORIES) is filled with .doc files (10
stories/rows).



The keywords that users of my database will use to find stories/.docs
wher these keywords appear are : pigs,cats,dogs,sheeps,horse. The result
of this search (STORYTITLE) should be ordered by the frequency of
appearance of keywords(and a percent of relevance should be displyed if
possible like ).



Also the authors want to have the possibility to choose one one animal
which is most important keyword in a prticular story, so when a user
search for i.e. the pig (regardles of how many pig hits are in the
story). So there will be a second rank list.



Particuar Questions:



1. I have populated the STORIES table using the image transfer
application from www.teca.com, but i do not know where the .doc files
physicaly stored. In a .mdf file or there where I have imported it
from (c:\), because when I select * from TABLE1 I get 0xD0CF11E0A1B1-
1AE1000000000000000000000000000000003E000300FEFF09000600000000000000-
0000000002000000C80000000000000000100000C900000001000000FEFFFFFF0000-
0000C6000000C7000000FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF-
FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF

which really tells me nothing.



2. How do I query these these STORIE-X.DOCs. For example, what are the
TSQL commans to display the titles where the pig appears



3. How to accomplish these rank lists



Particular answers are also welcome. But please in a "for a dummie"
manner. Every advice also.



Thank you x 1000,



Sash


--

Welcome, Sash,
While the question is simple, the answers can be many, i.e., there are
several ways to accomplish importing files (.txt, .doc, etc.) into SQL
Server. One method is to use TextCopy.exe (search the BOL for "textcopy") as
it ships with both SQL Server 7.0 and 2000 and is located in your \BINN
directory where you have SQL Server installed. An example of using Textcopy
called from xp_cmdshell (note, you will need to change your server name and
textcopy path):

use pubs
GO
DROP TABLE pub_info_copy
GO
CREATE TABLE pub_info_copy (
pub_id char(4) NOT NULL,
logo image NULL,
pr_info text NULL
)
GO
-- Note, you must "initialize" the image column "0xFFFFFFFF", before you can
insert into it with Textcopy.
insert pub_info_copy values('0736', 0xFFFFFFFF, 'None yet')
GO
-- Confirm row values BEFORE TEXTCOPY executed...
select * from pub_info_copy
GO

exec master..xp_cmdshell
'<path>textcopy -I -Usa -P<sa_password> -S<server> -Dpubs -Tpub_info_copy -C
logo -W"where pub_id = ''0736''" -F<drive_letter:\path>\algodata.gif'
GO

-- Confirm TEXTCOPY worked...
use pubs
GO
select * from pub_info_copy
GO

Additionally you can use the ADO Stream programming interface to SQL Server
as doc'ed in the following KB articles:

258038 (Q258038) HOWTO: Access and Modify SQL Server BLOB Data by Using the
ADO Stream Object
http://support.microsoft.com/?kbid=258038
309158 (Q309158) HOW TO: Read and Write BLOB Data by Using ADO.NET with C#
http://support.microsoft.com/default.aspx?scid=kb;EN-US;309158

308042 (Q308042) HOW TO: Read and Write BLOB Data by Using ADO.NET with
VB.NET
http://support.microsoft.com/default.aspx?scid=kb;EN-US;308042

Once you have loaded your files into the Image column, you also need to have
a "file extension" column added to your table, so that the FT Indexer
(MSSearch.exe) can "link" to the MS Word filter and pull out the keywords.
You can do this either via the "Full-Text Indexing Wizard" or via the
following SQL code:


exec sp_fulltext_catalog 'FTSCatalog','create'
exec sp_fulltext_table 'FTSTable','create','FTSCatalog','FTSTable_IDX'
exec sp_fulltext_column 'FTSTable','ImageCol','add', 0x0409, 'ExtCol'
exec sp_fulltext_column 'FTSTable','TextCol','add'
exec sp_fulltext_table 'FTSTable', 'activate'
go

-- Start FT Indexing...
exec sp_fulltext_catalog 'FTSCatalog','start_full'
go
-- Wait for FT Indexing to complete and check NT/Win2K Application log for
success/errors..

Note, I've attached a file (FTS_Images.sql) to this posting that has all the
necessary SQL code, however, if you have difficult getting access to it,
email me directly.

For your SQL FTS queries you should review SQL 2000 BOL titles
"CONTAINSTABLE" and the RANK as well as "Full-Text Search Recommendations"
for a good explanation of what RANK is all about.

As for "authors want to have the possibility to choose one animal which is
most important keyword in a particular story", how would the authors
determine what is the "most important keyword" in the story? As an
author-to-be (I'm researching and writing a book on FTS in Microsoft SQL
Server based products and web searching in general) and I have a difficult
time even deciding what is the most important keyword in a paragraph, let
alone a story! :-)

Regards,
John




[quoted text, click to view]


begin 666 FTS_Images.sql
M#0HM+0E&:6QE;F%M93H@7U1E<W1?1E137TEM86=E<U\S+G-Q; T*+2TM"5!U
M<G!O<V4Z(%1O('1E<W0@=&AE(&YE=R!&5$D@;V8@24U!1T4@9&%T871Y<&5S
M('=I=&@@=F%R:6]U<R!F:6QE('1Y<&5S+@T*+2T)"2!5<VEN9R!3:&EL;V@@
M4E1-("AB=6EL9" Q.30N,#$I;VX@5VEN,DL@*&)U:6QD(#(Q.34I#0H-"G5S
M92!P=6)S#0IG;PT*15A%0R!S<%]D8F-M<'1L979E;" G<'5B<R<@+2T@5&AE
M(&-U<G)E;G0@8V]M<&%T:6)I;&ET>2!L979E;"!I<R X,"X-"F=O#0IS96QE
M8W0@0$!V97)S:6]N("TM($UI8W)O<V]F="!344P@4V5R=F5R(" R,# P("T@
M."XP,"XQ.30@*$EN=&5L(%@X-BD@( T*9V\-"@T*:68@97AI<W1S("AS96QE
M8W0@*B!F<F]M('-Y<V]B:F5C=',@=VAE<F4@:60@/2!O8FIE8W1?:60H)T94
M4U1A8FQE)RDI#0H@(&1R;W @=&%B;&4@1E135&%B;&4-"F=O#0H-"D-214%4
M12!404),12!&5%-486)L92 H#0H@($ME>4-O; EI;G0@241%3E1)5%D@*#$L
M,2D@3D]4($Y53$P@#0H@(" @0T].4U1204E.5"!&5%-486)L95])1%@@4%))
M34%262!+15D@0TQ54U1%4D5$+ T*("!497AT0V]L"71E>'0@3E5,3"P-"B @
M26UA9V5#;VP):6UA9V4@3E5,3"P-"B @17AT0V]L"6-H87(H,RD@3E5,3"P@
M+2T@8V%N(&)E(&5I=&AE<B!S>7-N86UE(&]R(&-H87(H,RD-"B @5&EM95-T
M86UP0V]L"71I;65S=&%M<"!.54Q,( T**2!/3B!;4%))34%265T-"F=O#0H-
M"G-P7VAE;' @1E135&%B;&4-"F=O#0H-"BTM(%1R=6YC871E($1A=&$L(&EF
M(&YE8V5S<V%R>2XN#0I44E5.0T%412!404),12!&5%-486)L90T*9V\-"@T*
M+2T@26YS97)T(&1A=&$N+BX@*$YO=&4Z($EN:71A;&EZ:6YG($E-04=%(&-O