Re: search problem after upgrade from SQL2000 SP3 / w2k to SQL2000 SP4 / w2k3

ehrensenf is stored in the index as ehrensenf , ehre, 1, and senf. Perhaps
in the old version you were using the English or the neutral word breaker.

--
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
"tbh" <femdev@xxxxxxxxxxxxxxxxx> wrote in message
news:eoNZVMZrHHA.484@xxxxxxxxxxxxxxxxxxxxxxx
hi,

we have recently upgraded a production database using the following trick
(to minimize downtime):
- we replicated the database from db004 (old)[*] to db04 (new)[**] (merge
replication, updates every minute i think)
- [incidentally, i don't think it matters] we took this opportunity to
improve our (German) noise words from the supplied default list to a much
better list [***]
- once that was settled we created and populated the necessary fulltext
indexes on the new server [we did these steps via EntMgr GUI -- not
manually with SPs]
- we found a good time to swing all our production processes and clients
from the old machine to the new

there were a couple of glitches, but this all went pretty well.

we were just about to clean up (breaking the replication, disposing of the
old DB) when (alas, far too late -- we should have tested better: famous
last words) we discovered a *bad* problem with searching in the new
system:

CONTAINS searches are finding *way too many* hits. for example, searching
for "ehrensenf" (strange term) almost instantly finds about 5 hits in the
old DB. it times out or finds thousands of bogus hits in the new DB.

when i look at these bogus hits and try to fathom why the CONTAINS clause
succeded, the only clues i have found so far are that parts of the search
term are found (for example, "ehre", which is a word in its own right,
inside another word). but i have not asked for any special INFLECTIONAL
treatment or the like.

i'm looking for any clues about this (we have even opened a call to
Microsoft support, but they are evidently swamped at the moment, so I'm
trying this channel too; i plan to post a solution when we find one in
case anyone else runs into a similar problem.)

obviously several components have changed, which makes finding the culprit
non-trivial.

i'd be very grateful for any hints or ideas.

Tim Hanson


[*] old: SQL 2000 SP3 running on a Windows 2000 Server

[**] new: SQL 2000 SP4 running on a Windows 2003 Server

[***] we consulted http://www.ranks.nl/stopwords/german.html and
http://wortschatz.uni-leipzig.de/Papers/top100de.txt




.