Sunday, March 11, 2012

Does indexing on IMAGE fields work?

I am doing some prototyping using FTS in SQL 2005 Standard and am having
serious problems getting searching to work when using IMAGE or VarBinary(max)
fields. The index sortof works when using varchar fields. The only problem
with varchar is that it doesn't seem to always auto popluate when I make a
change (see other posting "FT Index doesn't Auto Populate"). However, when
trying the same thing with Image fields loaded with RTF data, the search
always returns nothing. The crawl log shows an error when indexing is first
done; (Error '0x80004005' ), however, it states that it will attempt to
reindex. The log shows 3 more attempts which fail and then finally one that
seems to succeed showing 6 records processed and 0 failed. Aside from that,
there is no other indication of a failure in any of the steps.
Some things I've tried;
- I've upgraded to SP1
- I'm always careful to enable the index after creating it (a new feature in
SP1)
- I've tried loading the DocType field for the associated image field with
several different types (doc, rtf, txt) and tried loading all three types of
documents into the image field. None work
- I've confirmed the iFilters are loaded on this server (eventhough I know
they should be there anyway as preloaded filters)
- I've created an index on a varchar(100) field that is in this same
database and table. The indexing and searching work fine, so, we know
there's nothing odd about the FTS, database, or table
Here's one of the basic queries I use to do the search, although, I've tried
several variations (none work)
SELECT Resume
FROM tbl_ServiceProviders
WHERE CONTAINS(Resume, ' "a*" ');
Having no practical experience with this Microsoft FTS feature, the obvious
question I'm starting to consider; Is this product ready for primetime? Has
anyone gone to production with SQL 2005 FTS? I'm being asked to use this to
build a search feature for an existing "job candidate searching system". I'm
getting a little concerned.
Thanks in advance for any insights anyone can offer
Mike
Maz
Have you looked at the full-text crawl logs? Also make sure the value of the
type column corresponds to the actual file type in the varbinary/image column.
See if this helps:
http://milambda.blogspot.com/2005/12/ifilter-error-bydesign-or-bybug.html
BTW, when searching for "a*" are you searching or words beginning with the
letter a? If you do, try again without the double quotes.
ML
http://milambda.blogspot.com/
|||Thanks ML for directing me to the article. Unfortunately, I've considered
all those elementary suggestions already.
I'm very frustrated with this product. I haven't even started implementing
a business solution for this yet! I'm just trying to do some very basic
prototyping and can't get 1) cataloging jobs to run, 2) auto populate to work
in realtime on a consistent basis, 3) Image fields to index.
Of course you might be thinking I might be a moron. I've considered that
myself However, I can assure you I am not. I've been programming for 20+
years and working with SQL Server for 10 years. I've never had such a hard
time performing basic prototyping using the provided technical documentation.
My conclusion so far; this product is terrible. It probably works, but you
need the "real world" manual to be productive with it. Unfortunately, it
seems so few people are using this product that there doesn't seem to be a
book available to offer real practical advice. The MS documentation only
instructs you on the theoretical behavior of this product. I can only
imagine the problems we will run into in a production environment with this.
Anyone know of any books based on practical experience using SQl 2005 FTS?
If not, I'm considering looking elsewere for my full text search needs.
Mike
"ML" wrote:

> Have you looked at the full-text crawl logs? Also make sure the value of the
> type column corresponds to the actual file type in the varbinary/image column.
> See if this helps:
> http://milambda.blogspot.com/2005/12/ifilter-error-bydesign-or-bybug.html
> BTW, when searching for "a*" are you searching or words beginning with the
> letter a? If you do, try again without the double quotes.
>
> ML
> --
> http://milambda.blogspot.com/
|||Spicy wrote on Sat, 3 Feb 2007 12:55:09 -0800:

> Having no practical experience with this Microsoft FTS feature, the
> obvious question I'm starting to consider; Is this product ready for
> primetime? Has anyone gone to production with SQL 2005 FTS? I'm being
> asked to use this to build a search feature for an existing "job candidate
> searching system". I'm getting a little concerned.
This bit I can answer - I've been using SQL 2005 FTS on public websites
since Feb 2006, and prior to that SQL 7 FTS for a few years (we skipped 2000
on our web sites, but use 2000 FTS internally). If you check http://www.compman.co.uk
, http://www.bookfellas.co.uk , or http://www.sprintbooks.co.uk , all
searches for keywords are done using FTS on SQL 2005. This is running on a
database with almost 700,000 products, and with FTIs on various columns for
each product.
Dan
|||That's somewhat conforting to hear. I suppose I'll keep trying to search the
internet for instances of this happening. I'm following the instructions
exactly. It just won't work for me. I've even tried changing the doc type
to include the dot because I saw it used both ways in some examples (e.g.
"rtf" and ".rtf") Nothing seems to work.
Maz
"Daniel Crichton" wrote:

> Spicy wrote on Sat, 3 Feb 2007 12:55:09 -0800:
>
> This bit I can answer - I've been using SQL 2005 FTS on public websites
> since Feb 2006, and prior to that SQL 7 FTS for a few years (we skipped 2000
> on our web sites, but use 2000 FTS internally). If you check http://www.compman.co.uk
> , http://www.bookfellas.co.uk , or http://www.sprintbooks.co.uk , all
> searches for keywords are done using FTS on SQL 2005. This is running on a
> database with almost 700,000 products, and with FTIs on various columns for
> each product.
> Dan
>
>
|||So, there are no errors in the crawl log?
ML
http://milambda.blogspot.com/
|||Take a look at my first post on this thread. There is an error that states
it will retry to index. It retries (and fails) several times, then it
finally succeeds and says that 6 rows were indexed and 0 failed. That was
the only thing that seemed exceptional. However, since it finally stated
that the index succeeded, I assumed that was not the problem. FYI: There
arre 100 records in the test database and 6 rows with data in the image
field. The reported numbers seem to match up with what I expected to see
Maz
"ML" wrote:

> So, there are no errors in the crawl log?
>
> ML
> --
> http://milambda.blogspot.com/
|||We index over 2 terabytes. It works well for us. The 4005 error is a generic
access denied, it could be caused by several factors. Can you try to extract
your data as text and index it that way? I would also remove a from your
noise word list and rebuild your catalog to remove the possibility that that
is causing your problems.
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
"Spicy Mikey" <Maz@.newsgroups.nospam> wrote in message
news:10663BF1-D3E4-4D58-8D7E-B1C9B325B5BB@.microsoft.com...
>I am doing some prototyping using FTS in SQL 2005 Standard and am having
> serious problems getting searching to work when using IMAGE or
> VarBinary(max)
> fields. The index sortof works when using varchar fields. The only
> problem
> with varchar is that it doesn't seem to always auto popluate when I make a
> change (see other posting "FT Index doesn't Auto Populate"). However,
> when
> trying the same thing with Image fields loaded with RTF data, the search
> always returns nothing. The crawl log shows an error when indexing is
> first
> done; (Error '0x80004005' ), however, it states that it will attempt to
> reindex. The log shows 3 more attempts which fail and then finally one
> that
> seems to succeed showing 6 records processed and 0 failed. Aside from
> that,
> there is no other indication of a failure in any of the steps.
> Some things I've tried;
> - I've upgraded to SP1
> - I'm always careful to enable the index after creating it (a new feature
> in
> SP1)
> - I've tried loading the DocType field for the associated image field with
> several different types (doc, rtf, txt) and tried loading all three types
> of
> documents into the image field. None work
> - I've confirmed the iFilters are loaded on this server (eventhough I know
> they should be there anyway as preloaded filters)
> - I've created an index on a varchar(100) field that is in this same
> database and table. The indexing and searching work fine, so, we know
> there's nothing odd about the FTS, database, or table
> Here's one of the basic queries I use to do the search, although, I've
> tried
> several variations (none work)
> SELECT Resume
> FROM tbl_ServiceProviders
> WHERE CONTAINS(Resume, ' "a*" ');
>
> Having no practical experience with this Microsoft FTS feature, the
> obvious
> question I'm starting to consider; Is this product ready for primetime?
> Has
> anyone gone to production with SQL 2005 FTS? I'm being asked to use this
> to
> build a search feature for an existing "job candidate searching system".
> I'm
> getting a little concerned.
> Thanks in advance for any insights anyone can offer
> Mike
>
>
> --
> Maz
|||Spicy wrote on Mon, 5 Feb 2007 06:01:02 -0800:

> That's interesting about the 4005 being an access problem. I wonder if
> all these problems are related to the same thing. In my other thread I
> talked about not being able to create the catalog job. It tells me it
> can't connect to the server remotely and suggested I be sure to have
> "remote access" turned on. I'm sure that's not the problem. I can access
> the database from my application using oledb and a standard connection
> string. I'm trying to creat the catalog from SMSS and am logged on as
> "sa".
I have no problems with SMSS or EM (for 2000/7), I am logged onto an account
on my local machine that exists as an admin on the SQL Server machine (it's
on a DMZ, not in our ADS, hence having to setup an account on the SQL
machine for me to admin it). It sounds as though you might have a problem
with the NTLM side of things - remember, the 'sa' login via SMSS is only for
SQL Server itself - communication with the Search service that is FTS is via
the NT subsystem itself, so you'll require your local login credentials to
match an admin level (or one that's able to communicate with the Search
service) credential on the SQL Server machine.
Dan
|||It is reported as an generic access problem, but it could be something
entirely different.
If you want to send me sample docs off line, I'll try to repro your problem.
We might get to the bottom of it faster this way.
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
"Spicy Mikey" <Maz@.newsgroups.nospam> wrote in message
news:CA379695-8D0C-46E7-8C65-F0F0AE6C5153@.microsoft.com...[vbcol=seagreen]
> That's interesting about the 4005 being an access problem. I wonder if
> all
> these problems are related to the same thing. In my other thread I talked
> about not being able to create the catalog job. It tells me it can't
> connect
> to the server remotely and suggested I be sure to have "remote access"
> turned
> on. I'm sure that's not the problem. I can access the database from my
> application using oledb and a standard connection string. I'm trying to
> creat the catalog from SMSS and am logged on as "sa".
> By the way, I tried several different search strings, not just "a*".
> That's
> not the problem. Rest assured, I've tried anything obvious and logical.
> Since FTS works great for you, I am assuming there is some "quirk" in the
> product that is burning me. Something that you can't know about from
> reading the documentation.
> --
> Maz
>
> "Hilary Cotter" wrote:

No comments:

Post a Comment