sql server full text search: Pranita,
Sure, here is a SQL script that uses Textcopy.exe (a utility that is shipped
with SQL 2000) to import files into an image column and then setup the FT
Catalog and run a Full Population. NOTE: you must also define a "file
extension" column in your table to "bind" with the IMAGE column, and use a
char(3) or sysname as the datatype for this column. Also, see SQL Server
2000 BOL titles "Filtering Supported File Types" and "Column-Level
Linguistic Analysis" for more info.

use pubs
go
exec sp_fulltext_database 'enable' -- ONLY FT-enable the Database once
go
CREATE TABLE FTSTable (
KeyCol int IDENTITY (1,1) NOT NULL
CONSTRAINT FTSTable_IDX PRIMARY KEY CLUSTERED,
TextCol text NULL,
ImageCol image NULL,
ExtCol sysname NULL, -- must be sysname ??? Test again using char(3) !!
TimeStampCol timestamp NULL
) ON [PRIMARY]
go
-- Insert data... NOTE: Textcopy must have the IMAGE column initalized prior
to inserting - 0xFF...
INSERT FTSTable values('Test TEXT Data for row 1', 0xFFFFFFFF, 'doc', NULL)
INSERT FTSTable values('Test TEXT Data for row 2', 0xFFFFFFFF, 'xls', NULL)
go
declare @query varchar(200)
-- NOTE: your Textcopy.exe PATH may differ!
set @query = 'D:\MSSQL80\MSSQL$SQL80\Binn\textcopy /s '+@@servername+' /u sa
/p /d pubs /t FTSTable /c ImageCol /f D:\JKane\Docs\<Doc_name_here.doc> /i
/k 5000 /w "where KeyCol=1"'
print @query
exec master..xp_cmdshell @query
go
-- Create FT Catalog and setup columns and activate
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' --, 0x0409,'ExtCol'
exec sp_fulltext_table 'FTSTable', 'activate'
go
-- Start a Full Population
exec sp_fulltext_catalog 'FTSCatalog','start_full'
go
while ( fulltextcatalogproperty ('FTSCatlog','populatestatus') <> 0 )
waitfor delay '0:00:05'
go

-- Confirm Full Population succeeded & review the server's Application Event
log...
-- Test a FTS query
select KeyCol, TextCol from FTSTable where
contains(*,'<search_word_in_doc>') order by KeyCol
go

-- Clean-up
exec sp_fulltext_table 'FTSTable','drop'
exec sp_fulltext_Catalog 'FTSCatalog','drop'
drop table FTSTable
-- <eof>

Regards,
John



[quoted text, click to view]

I use vbscript for this

Set objConn = CreateObject("ADODB.Connection")
Set objRS = CreateObject("ADODB.RecordSet")
Set objStream=CreateObject("ADODB.Stream")
objConn.Open "Provider=SQLOLEDB;data Source=kpannin3
\new;Initial Catalog=TEXT;User Id=sa;Password=LinchiRulez"
Set objFileSystem=createobject
("Scripting.FileSystemObject")
Set objDir=objFileSystem.GetFolder("c:\htm")
for each objFile in objDir.Files
count=count+1
wscript.echo objFile.name
objConn.Execute "insert into TextTable (IMAGECol) values
('jibberish')"
objRs.Open "select textcol from texttable where pk=" &
count, objConn, 1, 3
objStream.Type = 1
objStream.Open
objStream.LoadFromFile objFile.Path
objRs.Fields("TextCol").Value=objStream.Read
objRs.Update
objRs.Close
objStream.Close
next
objConn.Close
Set objStream=nothing
Set objShell =nothing
Set objConn=nothing
Set objFileSystem=nothing
Set objDir=nothing

[quoted text, click to view]
sp_fulltext_table 'FTSTable','create','FTSCatalog','FTSTabl
e_IDX'
[quoted text, click to view]