Indexing Word Docs

We currently have an application that OCRs a tif image and places the
recognized text in a SQL table.
The table is then indexed by the FTS service.
The app then allows you to search for any of the text and display the
corresponding tif image in a viewer.
I would also like to be able to search WORD docs for their contents using
the same catalog.
What is the proper manner to have the WORD docs indexed by the FTS service?
Do I need to extract the text from the WORD doc and store it in the table
much like the recognized text
from the OCR process?
Thanks
John,
What is the relationship between FTS and Indexing Service?
It looks like the Indexing Service maintains a catalog much the same as FTS.
We have support for WORD in our app already by storing the WORD doc in our
file warehouse on the file system.
We can display the .doc file in our viewer the same as a .tif image.
We currently don't have functionality to search for data in the WORD docs,
only text from the OCR process.
Since the WORD file is already stored in the file system and referenced by
our application, I was wondering about the feature that is titled "Full-text
Querying of File Data"
It looks like it uses the Index Service to allow searching for data in files
on the file system.
Wouldn't that work for my scenario?
It appears that when we want to search for data contained in a WORD doc, we
would use the SCOPE function in our query. Otherwise, we continue to search
for text from the OCR process.
Can you provide some insight?
Thanks
"John Kane" <jt-kane@.comcast.net> wrote in message
news:O7TiL6BWEHA.2928@.tk2msftngp13.phx.gbl...
> Binder,
> What version of SQL Server (2000 or 7.0) and on what OS platform (NT4.0,
> Win2K, or Win2003) is it installed? Could you post the full output of
> SELECT @.@.version -- as this is helpful to answering your question.
> If you are using SQL Server 2000, you can use it's new feature (this
feature
> is not present in SQL 7.0) - from SQL Sever 2000 BOL title "Filtering
> Supported File Types". This feature allows you to store the binary version
> of the MS Word document and then in your table define a file extension
> column and populate it with the correct values ("doc" for MS Word
document)
> and then run a Full Population and then you can use the CONTAINS or
FREETEXT
> quires to FTS the contents of these files stored in a sql table>
> If you are using SQL Server 7.0, you will need to setup a process to
extract
> the MS Word text and then store this text in a TEXT column and the FT
Index[vbcol=seagreen]
> that column, much as you do for your OCR'ed data.
> Regards,
> John
>
> "Binder" <rgondzur@.NO_SPAM_aicsoft.com> wrote in message
> news:eIXu546VEHA.2716@.tk2msftngp13.phx.gbl...
using[vbcol=seagreen]
> service?
table
>
|||System Parameters:
Windows 2000 Server
Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
Aug 6 2000 00:57:48
Copyright (c) 1988-2000 Microsoft Corporation
Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
"Binder" <rgondzur@.NO_SPAM_aicsoft.com> wrote in message
news:OcUOqQGWEHA.1012@.TK2MSFTNGP09.phx.gbl...
> John,
> What is the relationship between FTS and Indexing Service?
> It looks like the Indexing Service maintains a catalog much the same as
FTS.
> We have support for WORD in our app already by storing the WORD doc in our
> file warehouse on the file system.
> We can display the .doc file in our viewer the same as a .tif image.
> We currently don't have functionality to search for data in the WORD docs,
> only text from the OCR process.
> Since the WORD file is already stored in the file system and referenced by
> our application, I was wondering about the feature that is titled
"Full-text
> Querying of File Data"
> It looks like it uses the Index Service to allow searching for data in
files
> on the file system.
> Wouldn't that work for my scenario?
> It appears that when we want to search for data contained in a WORD doc,
we
> would use the SCOPE function in our query. Otherwise, we continue to
search[vbcol=seagreen]
> for text from the OCR process.
> Can you provide some insight?
> Thanks
>
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:O7TiL6BWEHA.2928@.tk2msftngp13.phx.gbl...
> feature
version
> document)
> FREETEXT
> extract
> Index
> using
> table
>
|||Binder,
Q. What is the relationship between FTS and Indexing Service?
A. While they use the same underlying Microsoft Search Technology, they full
text index different servers. Indexing Service handles the server's files on
its local disk drive, while FTS (or really the "Micrsoft Search" service
[mssearch.exe]) full text indexes textaul (char, nvarchar, text, etc.)
columns in SQL Server tables. Yes, it seems to me that using the Indexing
Service, should work for you.
What is the name of your app? Does it support SQL Server 2000? If so, does
it support the storage of MS Word documents in columns that are defined with
the IMAGE datatype? Is the feature that is titled "Full-text Querying of
File Data", a feature of your app, or are you referring to the feature of
SQL Severer (version) ?
In addition to SQL Server's Full-text Search (FTS) component, you can also
define a "Linked Server" to the Indexing Service via using MSIDX, the "OLE
DB Provider for Microsoft Indexing Service". You would define this linked
server via sp_addlinkedserver. Below is an example from SQL Server 2000
Books Online:
G. Use the Microsoft OLE DB Provider for Indexing Service
This example creates a linked server and uses OPENQUERY to retrieve
information from both the linked server and the file system enabled for
Indexing Service.
EXEC sp_addlinkedserver FileSystem,
'Index Server',
'MSIDXS',
'Web'
GO
USE pubs
GO
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'yEmployees')
DROP TABLE yEmployees
GO
CREATE TABLE yEmployees
(
id int NOT NULL,
lname varchar(30) NOT NULL,
fname varchar(30) NOT NULL,
salary money,
hiredate datetime
)
GO
INSERT yEmployees VALUES
(
10,
'Fuller',
'Andrew',
$60000,
'9/12/98'
)
GO
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'DistribFiles')
DROP VIEW DistribFiles
GO
CREATE VIEW DistribFiles
AS
SELECT *
FROM OPENQUERY(FileSystem,
'SELECT Directory,
FileName,
DocAuthor,
Size,
Create,
Write
FROM SCOPE('' "c:\My Documents" '')
WHERE CONTAINS(''Distributed'') > 0
AND FileName LIKE ''%.doc%'' ')
WHERE DATEPART(yy, Write) = 1998
GO
SELECT *
FROM DistribFiles
GO
SELECT Directory,
FileName,
DocAuthor,
hiredate
FROM DistribFiles D, yEmployees E
WHERE D.DocAuthor = E.FName + ' ' + E.LName
GO
Regards,
John
"Binder" <rgondzur@.NO_SPAM_aicsoft.com> wrote in message
news:OcUOqQGWEHA.1012@.TK2MSFTNGP09.phx.gbl...
> John,
> What is the relationship between FTS and Indexing Service?
> It looks like the Indexing Service maintains a catalog much the same as
FTS.
> We have support for WORD in our app already by storing the WORD doc in our
> file warehouse on the file system.
> We can display the .doc file in our viewer the same as a .tif image.
> We currently don't have functionality to search for data in the WORD docs,
> only text from the OCR process.
> Since the WORD file is already stored in the file system and referenced by
> our application, I was wondering about the feature that is titled
"Full-text
> Querying of File Data"
> It looks like it uses the Index Service to allow searching for data in
files
> on the file system.
> Wouldn't that work for my scenario?
> It appears that when we want to search for data contained in a WORD doc,
we
> would use the SCOPE function in our query. Otherwise, we continue to
search[vbcol=seagreen]
> for text from the OCR process.
> Can you provide some insight?
> Thanks
>
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:O7TiL6BWEHA.2928@.tk2msftngp13.phx.gbl...
> feature
version
> document)
> FREETEXT
> extract
> Index
> using
> table
>

indexing image columns

Hi,
I'm building a search engine for a website using sql server 2000 (sp3).
My development invironment is Windows XP Pro and the production server
is Windows 2003, and I'm getting the same problem on both machines.
I have two tables in the cataolg, one contaning only text fields
(varchar and ntext). The results come out great for this table, no
problem here. The second table has an image field that can contain just
about any kind of documents: .txt, .doc, .pdf, .xls, .mpg, .zip, etc.
And this image column can sometimes be empty for cetain records. There's
also a column indicating the file type and I'm using the file extension
for this. I wasn't sure what to put here I tried the mime type, the
extension, with and without the period this never changed anything. I
read somewhere that it should be the file extension with the period so
I've set it back to that.
I've done a lot of searching and reading in the past few days but can't
find the problem. I've tryed searches on various types of documents,
including .txt, .doc and .pdf. I'm not absolutely sure but I think my
image column is't being indexed at all; this same table also has a
"title" and "description" field included in the index and if I search
for text contained in either of those 2 columns they turn up in the results.
Any ideas?
Any help would be much appreciated.
tia
Lucas,
Yes. First of all, could you post the full output of -- SELECT @.@.version --
as well as the table schema of both your tables (via sp_help <table_name>)
as will help in understanding your environment. The datatype, size and
nullablity of the "file extension" column is very important in getting this
to work correctly. You can include or exclude the "." period when populating
the values in your "file extension" column, but then you will need to define
it as a varchar(4) or you can use the sysname datatype.
As you second table contains an image column and the "file extension"
column, it can only hold binary file types, such as doc, .pdf, .xls, .mpg,
..zip, but not .txt. For text (.txt) files, you must store this type file in
a Text or NText datatype as the pure text will be FT Indexed without the
file extension. For non-MS Office file types, such as Adobe PDF, MPG and ZIP
files, you will need to install 3rd party IFilters that support these file
types, these can be downloaded from:
Adobe PDF IFilter v6.0:
http://www.adobe.com/support/downloa...11&fileID=2457
Zip IFilter:
http://www.ifiltershop.com/zipfilter.html
Zip IFilter:
http://www.4-share.com/
mp3 (MEPG) IFilter
http://www.meticulus.com/mp3filter.html
Microsoft Office file types (.doc, .xls, .ppt) and text (.txt) and HTML
(.htm) files are FT Indexed out-of-the-box by SQL Server 2000. See SQL
Server 2000 BOL title "Filtering Supported File Types" from more info.
Finally, you should always review the server's Application event log for
information on the success &/or failure of FT Indexing specific file types.
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"lucas" <lucarc@.hotmail.qc> wrote in message
news:xgXDd.32709$Y61.1126448@.wagner.videotron.net. ..
> Hi,
> I'm building a search engine for a website using sql server 2000 (sp3).
> My development invironment is Windows XP Pro and the production server
> is Windows 2003, and I'm getting the same problem on both machines.
> I have two tables in the cataolg, one contaning only text fields
> (varchar and ntext). The results come out great for this table, no
> problem here. The second table has an image field that can contain just
> about any kind of documents: .txt, .doc, .pdf, .xls, .mpg, .zip, etc.
> And this image column can sometimes be empty for cetain records. There's
> also a column indicating the file type and I'm using the file extension
> for this. I wasn't sure what to put here I tried the mime type, the
> extension, with and without the period this never changed anything. I
> read somewhere that it should be the file extension with the period so
> I've set it back to that.
> I've done a lot of searching and reading in the past few days but can't
> find the problem. I've tryed searches on various types of documents,
> including .txt, .doc and .pdf. I'm not absolutely sure but I think my
> image column is't being indexed at all; this same table also has a
> "title" and "description" field included in the index and if I search
> for text contained in either of those 2 columns they turn up in the
results.
> Any ideas?
> Any help would be much appreciated.
> tia
|||Hi John,
Thanks for your response. I finally got it to work; it was nothing more
than an error in my query, I wasn't actualy ever searching my image
column.....d'oh!
It's working fine now.

Indexing image - file size limit?

According to the Books Online information on full-text indexing, there
are limits on the size of a file that can be indexed in an image
column: 16MB filesize, 256 KB of filtered text. I've exceeded those
limits in my testing (with Word docs), and still appear to be able to
access information in those files with CONTAINS. Is the documentation
out of date? Are there only certain conditions under which those limits
apply? The word I'm searching for appears only at the end of the test
document, so it's not indexing only the first part of the file...
Since it seems to be a common question here, this is my @.@.version:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002
14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise
Edition on Windows NT 5.2 (Build 3790: )
And, just for clarity, I don't have any problem with SQL Server
indexing more than I had planned on, I just don't want any surprises
down the road.
Thanks for any ideas you have,
Joel
Last time I tested, when the hard limit was exceeded the remaining content
was not indexed.
So if you index a document containing more than 256k of text, and then put
the word rats at the end, and then tried to search on the word rats, you
would not get hits to this row, if the word rats did not occur in the first
256k of text.
One question for you is did these word docs contains any images? Images will
not be indexed, and can swell the document size, without pushing you over
the 256 k limit.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
<nospamforjoel@.yahoo.com> wrote in message
news:1105371120.743181.194610@.c13g2000cwb.googlegr oups.com...
> According to the Books Online information on full-text indexing, there
> are limits on the size of a file that can be indexed in an image
> column: 16MB filesize, 256 KB of filtered text. I've exceeded those
> limits in my testing (with Word docs), and still appear to be able to
> access information in those files with CONTAINS. Is the documentation
> out of date? Are there only certain conditions under which those limits
> apply? The word I'm searching for appears only at the end of the test
> document, so it's not indexing only the first part of the file...
> Since it seems to be a common question here, this is my @.@.version:
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002
> 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise
> Edition on Windows NT 5.2 (Build 3790: )
> And, just for clarity, I don't have any problem with SQL Server
> indexing more than I had planned on, I just don't want any surprises
> down the road.
> Thanks for any ideas you have,
> Joel
>
|||No, the documents that are confusing me did not have any images. They
were just a bunch of text, pasted repeatedly. I ran them through
filtdump, to make sure they really did have more than 256K of text. The
test you describe is exactly what I did--I put words at the very end of
the document that I was sure weren't in the document before, and once
the catalog rebuilt, I searched for them, and found them.
Thanks,
Joel
|||Let me try this myself. I did try this several years ago so this may have
changed with a recent sp.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
<nospamforjoel@.yahoo.com> wrote in message
news:1105386126.803682.118060@.c13g2000cwb.googlegr oups.com...
> No, the documents that are confusing me did not have any images. They
> were just a bunch of text, pasted repeatedly. I ran them through
> filtdump, to make sure they really did have more than 256K of text. The
> test you describe is exactly what I did--I put words at the very end of
> the document that I was sure weren't in the document before, and once
> the catalog rebuilt, I searched for them, and found them.
> Thanks,
> Joel
>
|||Joel,
Q. Is the documentation out of date?
A. Actually, it is wrong as there is a DOC bug filed for this limited in the
BOL title "Filtering Supported File Types" - "Note For full-text indexing,
a document must be less than 16 megabytes (MB) in size and must not contain
more than 256 kilobytes (KB) of filtered text" and this limit can be
over-ridden via KB article: 308771 (Q308771) "PRB: A Full-Text Search May
Not Return Any Hits If It Fails to Index a File" at
http://support.microsoft.com/default...;en-us;308771. and the
FilterProcessMemoryQuota registry key value. However, you should be careful
in making adjustments to this registry key and incrementally increase it
based upon your server's memory and avg. file sizes.
Q. Are there only certain conditions under which those limits apply?
A. Not specific conditions, but you should ensure that you have enough disk
free space (at least always 15% free) at all times where you have your FT
Catalog folder located as temp. files are written out as needed for the
processing of large files at the same location.
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
<nospamforjoel@.yahoo.com> wrote in message
news:1105386126.803682.118060@.c13g2000cwb.googlegr oups.com...
> No, the documents that are confusing me did not have any images. They
> were just a bunch of text, pasted repeatedly. I ran them through
> filtdump, to make sure they really did have more than 256K of text. The
> test you describe is exactly what I did--I put words at the very end of
> the document that I was sure weren't in the document before, and once
> the catalog rebuilt, I searched for them, and found them.
> Thanks,
> Joel
>
|||I'm not entirely sure I'm clear. If I'm reading that article right, it
looks like there is still some point at which indexing a document will
fail due to lack of memory. However, that point cannot be determined by
examining the file size of the document. Is that accurate?
Thanks,
Joel
John Kane wrote:
> Joel,
> Q. Is the documentation out of date?
> A. Actually, it is wrong as there is a DOC bug filed for this limited
in the
> BOL title "Filtering Supported File Types" - "Note For full-text
indexing,
> a document must be less than 16 megabytes (MB) in size and must not
contain
> more than 256 kilobytes (KB) of filtered text" and this limit can be
> over-ridden via KB article: 308771 (Q308771) "PRB: A Full-Text Search
May
> Not Return Any Hits If It Fails to Index a File" at
> http://support.microsoft.com/default...;en-us;308771. and
the
> FilterProcessMemoryQuota registry key value. However, you should be
careful
> in making adjustments to this registry key and incrementally increase
it
> based upon your server's memory and avg. file sizes.
> Q. Are there only certain conditions under which those limits apply?
> A. Not specific conditions, but you should ensure that you have
enough disk
> free space (at least always 15% free) at all times where you have
your FT
> Catalog folder located as temp. files are written out as needed for
the[vbcol=seagreen]
> processing of large files at the same location.
> Regards,
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> <nospamforjoel@.yahoo.com> wrote in message
> news:1105386126.803682.118060@.c13g2000cwb.googlegr oups.com...
They[vbcol=seagreen]
The[vbcol=seagreen]
end of[vbcol=seagreen]
once[vbcol=seagreen]
|||You're welcome, Joel,
Yea, the RESOLUTION section states "Unfortunately, there is no way to
calculate directly from the size of the document to be full-text indexed how
much memory the filter process needs. The memory quota only exists to
protect against badly written filters, and they do spike to large amounts if
some bogus size contains a negative number. The quota itself can be made
larger, as long as it is finite. "
While no upper limit size for documents to be FT Indexed is documented, you
can increase the amount of text to be indexed by modifying the
FilterProcessMemoryQuota registry key value and you need to test your
documents on your server to get a feel for what is the "finite" limit and
monitor the server's application event log for "Microsoft Search" source
events for very large files that fail.
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
<nospamforjoel@.yahoo.com> wrote in message
news:1105452953.633306.318410@.f14g2000cwb.googlegr oups.com...
> I'm not entirely sure I'm clear. If I'm reading that article right, it
> looks like there is still some point at which indexing a document will
> fail due to lack of memory. However, that point cannot be determined by
> examining the file size of the document. Is that accurate?
> Thanks,
> Joel
> John Kane wrote:
> in the
> indexing,
> contain
> May
> the
> careful
> it
> enough disk
> your FT
> the
> They
> The
> end of
> once
>
|||I just tried it again. I indexed a 32 Mg text and a 16 Mg word doc and have
confirmed that at least first 256 k of extracted text is indexed, but that
tokens at the end of the documents are not. Any textual data after this 256k
boundary appears to be ignored.
I have the same version of SQL Server as you, only I am running on Win2k.
Let me try with Win2003.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:u6I6P509EHA.3376@.TK2MSFTNGP12.phx.gbl...
> Let me try this myself. I did try this several years ago so this may have
> changed with a recent sp.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> <nospamforjoel@.yahoo.com> wrote in message
> news:1105386126.803682.118060@.c13g2000cwb.googlegr oups.com...
>

indexing document stored in IMAGE fields

Hi guys,
in the Full Text Retrieval documentation of MS SQLserver 2000 it's said that the document stored in IMAGE
fields are "filtered" using the Microsoft provided filters (for these file extensions: .doc, .xls, .ppt, .txt
and .htm) or third party filters (e.g. Adobe for .pdf).
Little after there's a note stating that "For full-text indexing, a document must be less than 16 megabytes (MB)
in size and must not contain more than 256 kilobytes (KB) of filtered text.".
While I can check if a file is larger than the max supported size, how can I check if a document contains more
than 256 KB of filtered text ? Is this information "exported" in some way by the filter applied to the document ?
If I store a document that does not satisfy the MSSearch requirements (size > 16MB or "filtered size" > 256 KB), which
actions are made by MSSearch ? Does indexing simply ignore it ?
Many THXS for your kind reply
MadMax
The best way to do this is to get filtdump from the platform sdk and do this
filtdump -b mydoc.doc >c:\out.out and then measure the size of the output.
In other versions of Microsoft Search products there were limits of the amount fo text per document that would be indexed. You could adjust this with a registry key setting. Any bytes over this interval would not be retrieved or indexed.
There are settings within MSSearch which allows you to control the maximum raw size of a document you are indexing but AFAIK there is no setting to allow you to increase the maximum amount of textual data it will index.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
|||THX very much Hilary.
Do you know what happen if the indexed document is bigger than the MSSearch limits (size 16MB, filtered size 256KB) ?
Is the document indexed (may be only for the part <16MB / filtered size < 256) or the indexing process fails completely ?
Max
|||My understanding is that only the first 16 M is extracted and only the first 256 k of text indexed.
The rest is ingnored.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
|||Max, Hilary,
I posted this reply in Nov. 2003, on this same subject... "I believe that
this is a DOC bug, i.e., a bug or incorrect information in
Books Online (BOL). To the best of my knowledge, no public KB exists for
this bug, however a related KB article - 308771 (Q308771) "PRB: A Full-Text
Search May Not Return Any Hits If It Fails to Index a File" at
http://support.microsoft.com/default...b;en-us;308771 has
information on the use of the Registry key: FilterProcessMemoryQuota.
You can control the size of the FT Index doc via Registry Key
FilterProcessMemoryQuota and setting it's value. Specifically,
HKLM\Software\Microsoft\Search\1.0\gathering
manager\filterProcessMemoryQuota, (DWORD).
It should default to 25MB and you can make it larger as it only affects the
limit for memory usage in the daemon [MSSdmn] process. In addition to the
memory allocated to SQL Server, it is recommended that a minimum of 15 MB of
RAM be reserved for the Microsoft Search service and a maximum of 512 MB of
RAM be allocated for the Microsoft Search service. If you plan on FT
Indexing large documents, you will need to set aside more memory for the
"Microsoft Search" (mssearch.exe) service and TEST the performance FT
Indexing very large documents as well as ensure that you have enough free
disk space on you system drive and the drive where your FT Catalogs reside
at all times."
Regards,
John
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:32335399-ADC7-45AD-B179-8D97C46CBC73@.microsoft.com...
> My understanding is that only the first 16 M is extracted and only the
first 256 k of text indexed.
> The rest is ingnored.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>