|
This article is a brief summary of several tips & tricks I have learned
through working with the Full-Text features in SQL Server.
Invalid Catalog Path:
After installing SQL Server 2000 on a new machine and re-attaching
the databases from tape back-up, I experienced some difficulties in getting the Full-Text Indexing
to work properly.
I received an error referencing an invalid location for the Full-Text
catalog from within Enterprise Manger
when I tried to run a full population of the catalog. The stored procedures for
disabling and removing Full-Text indexes and catalogs did not resolve the issue. In poking around, I
found the problem stemmed from the
fact that on the previous machine, the catalog was located on the F: drive.
On the new machine, there was no F: drive.
The Full-Text Indexing wizard in SQL 2000 does not allow the user to alter the location
of an existing catalog. It only lets the user create a new catalog. I tried to create a
new catalog in a new location as a work around, but because SQL could not resolve the
erroneous location of the previous catalog, I could not complete the wizard.
To fix the problem, I changed the SQL Server behavior
to allow modifications to the system catalogs.
I looked in the sysFullTextCatalogs table in the current database and
changed the 'path' field value to the new location.
(If the value is NULL, it means a path was not given at setup and the default installation path was used.)
This allowed me to modify the Full-Text Indexing on the new machine. (Remember to change the server behavior back
to its original setting.)
Incremental Population Discrepencies:
Incremental Full-Text Index population in SQL
7.0 and 2000 exhibit different behaviors. The behavior of SQL 7.0's Full-Text catalog population is
documented,
but sometimes hard to find, so I wanted to discuss it here.
On my SQL 7.0 machine, I scheduled an incremental population.
When I checked in to see if it had run, it had not. In SQL 2000,
I can schedule an incremental population and not have to worry about first running a full population.
SQL 2000 knows if it is the first population or if it is a subsequent population. SQL 7.0 does not
have this feature. If you schedule an incremental population without running a full
population first, the incremental population will not run. And when you run your full-text query, it
will return nothing.
(Helpful hint: Make sure you have a field of type TimeStamp in your table. Without one, you cannot
properly run an incremental population.)
Full-Text Querying Discrepencies:
Issue 1
There are some differences between SQL 7.0 and 2000 in their Full-Text
Querying capabilites. SQL 7.0 has limitations in the number of 'CONTAINS' clauses it can process
at any one time (in my testing, it is around 16). I have not been able to find any specific documentation on this
issue, but SQL 2000 does not seem to have this limit. Below is a brief reference from Microsoft
on this issue:
If you are using multiple CONTAINS or FREETEXT predicates in your SQL query and
are experiencing poor full-text search query performance, reduce the number of
CONTAINS or FREETEXT predicates or using "*" to use all full-text indexed
columns in your query.
Issue 2
In SQL 7.0, if there are excessive grouping parenthesis
(even though they match up), the query will hang. Even when the command
timeout property is set, the query will hang past the command timeout value assigned, and you will receive an
error message of 'Connection Failure -2147467259'. When the extra parenthesis are removed, the query
executes fine. In SQL 2000 the original query runs with no problems.
Issue 3
When a Full-Text query in SQL 7.0
contained a single noise word, I would receive
the error 'Query contained only ignored words'.
SQL 2000, handled the noise words and returned the query results. In SQL 7.0,
I had to remove all noise words from the query for it to run successfully.
Here is a recommendation from Microsoft
pertaining to this issue:
You also may encounter Error 7619, "The query contained only ignored
words" when using any of the full-text predicates in a full-text query, such as
CONTAINS(pr_info, 'between AND king'). The word "between" is an ignored or noise
word and the full-text query parser considers this an error, even with an OR
clause. Consider rewriting this query to a phrase-based query, removing the
noise word, or options offered in Knowledge Base article Q246800, "INF:
Correctly Parsing Quotation Marks in FTS Queries". Also, consider using Windows
2000 Server: there have been some enhancements to the word-breaker files for
Indexing Services.
For more information on Full-Text Indexing and Querying, visit
Microsoft MSDN.
To view my other articles, feel free to visit my homepage.
Server Properties:
sysfulltextcatalogs table:
(noise.enu - located in ...\FTDATA\SQLServer\Config\)
about
1
after
2
all
also
3
an
4
and
5
another
6
any
7
are
8
as
9
at
0
be
$
because
been
before
being
between
both
but
by
came
can
come
could
did
do
does
each
else
for
from
get
got
has
had
he
have
her
here
him
himself
his
how
if
in
into
is
it
its
just
like
make
many
me
might
more
most
much
must
my
never
now
of
on
only
or
other
our
out
over
re
said
same
see
should
since
so
some
still
such
take
than
that
the
their
them
then
there
these
they
this
those
through
to
too
under
up
use
very
want
was
way
we
well
were
what
when
where
which
while
who
will
with
would
you
your
a b c d e f g h i j k l m n o p q r s t u v w x y z
|
