December 4, 2003 at 11:29 am
Hi,
I would like to use SQL Server 2000 Full-text indexing on various file extension types such as XLS, DOC, HTM and PDF, stored in a table. I have done the following:
1.Created a table as the following:
CREATE TABLE [dbo].[WPFTImages] (
[WPFTImageId] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[WorkPaperId] [int] NOT NULL ,
[ImageContent] [image] NOT NULL ,
[FileExt] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
2.We use the ColdFusion to load the binary documents into the image column:
<cfquery name="insertBinaryData" datasource="regulatory" dbtype="odbc">
INSERT INTO WPFTImages (WorkPaperId, FileExt, ImageContent)
VALUES
(
#getworkpapers.workpaperid#,
'#getworkpapers.fileextension#',
<cfqueryparam value="#binData#" cfsqltype="cf_sql_blob">
)
</cfquery>
My sense is that this is effective, because the documents can be retrieved and reconstructed (e.g., as Word documents) without error.
3.Used Full-text Index Wizard to create catalog using dbo.WPFTImages table on ImageContent column using FileExt column (doc, xls, htm, pdf) as FileType binding column.
4.Performed full population of the catalog.
When I use the following query with a word that I know for sure exists in the document stored in ImageContent field, I get 0 row returned:
select WPFTImageId
from WPFTImages
WHERE Contains (ImageContent, '"bank"')
Please advise. Also, I did not see PDF files listed as Full-Text index supporting file type in BOL. Is it supported please?
Much thanks in advance.
Sang Nahm
December 9, 2003 at 8:00 am
This was removed by the editor as SPAM
December 11, 2003 at 4:16 pm
I have built several FTI solutions but never using anything other than pure text. I would be interested in how FTI works with different formats of binary data from within a table. If you could send me some links as to how that works, I would appreciate it.
For troubleshooting purposes, have you tried to setup and query a simple 'text' content FTI catalog and query against it?...just to make sure it isnt't something in the basic FTI configuration.
Edited by - jwiner on 12/11/2003 5:30:44 PM
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply