noise word bug...
Fadi Fakhouri
Hi Chris,
You have few options
1) remove the noise words yourself from the query
2) have an empty noise word file
3) a combination of the above, ( e.g. remove numbers and single letters
yourself from the query) and remove anything more than just numbers / single
letters form the noise word file
But to maintian correctness, we can not strip the noise words out for
you.....
Finaly,
SP3 is not SQL2000 !!!!
SP3, are bug fixes and QFE's rooled into a service pack,
SQL2000 has lots more functionality and improvments....
Thanks
Fadi Fakhouri
"Chris Borden" <cbo...@beckett.com> wrote in message
news:Ok0B$zINAH...@cppssbbsa02.microsoft.com...
> Hello.. I posted this a while back on the sqlserver group. now that we've
> got this more focused group, maybe someone can shed some light & at least
> tell me if this is fixed in SQL 2000
>
> one suggestion for this is, of course, to remove the noise word list.
while
> this might work, it'll create problems of it's own (not to mention
> additional workload). I could also parse out the noisewords from the
query
> beforehand, so maybe that's the only answer. oh, and does SP3 = SQL 2k ?
:)
>
>
> *********************************************************
> Hi all
>
> I'm running into Full-Text search errors when we include noise words in
the
> search criteria. Using containstable on non-noise words returns ranking &
> keys of correct items.
>
> Typically, the search looks like this:
> (we parse out the words and separate them with the "NEAR" clause, or the ~
> shortcut)
>
>
> select * from containstable(tblItems, szShortDesc,
> '1997~Donruss~Preferred~Precious~3~Metals~Greg~Maddux')
>
> If we pull out the "3", it works fine. As is, however, it returns
>
> Search on full-text catalog 'ItemsFTCatalog' for database ID 5, table ID
> 661577395 with search condition
> '1997~Donruss~Preferred~Precious~3~Metals~Greg~Maddux' failed with unknown
> result (390031).
>
> What I was able to determine is that the result is some sort of ASCII
> translation of the offending word/phrase.
>
> If the search word is:
> "a", the result code is (61)
> "n" = (6e)
> "an" = (6e0061)
> "the" = (680074)
>
>
> so it looks like it takes the last 2 letters of the word, flips them &
> returns their ASCII values w/ 00 between them.
> The noise words file contains all of the default words and hasn't been
> modified.
>
> we're running SQL 7.0 SP2. running on NT 4.0, SP6a
>
> Any ideas ?
>
> -Chris Borden
> Web Developer, Beckett.com
>
>
>
>
>
Chris Borden
post, so not much help here.
Your answer was also very "safe". I know that you have certain posting
guidelines that you must adhere to, but are you not allowed to simply say
"this capability of our product is broken. The only solution to this is a
workaround...."
Oh, and my comment (followed by a smiley) about "SQL 2k = SP3" was more of a
jab than a question. In other words: "Rather than fixing the existing
product, we'd much rather you buy our new product." Maybe that's unfair,
but how many questions here are answered with " this feature has been
corrected / improved in SQL 2000" ?
However, now that SP3 *IS* out for SQL 7, I'm curious as to what it fixes
other than all the routine hotfixes, etc... I guess I'll go dig up some
doc's on that....
Cheers!
-Chris
"Fadi Fakhouri" <fa...@microsoft.com> wrote in message
news:Of$#RVZTA...@cppssbbsa02.microsoft.com...
John Kane
This issue is less with SQL Server (7.0 or 2000) and more of an issue with
the OS wordbreaker that is used to break the words based upon language
specific word boundaries (word-breaking) and conjugating verbs (stemming).
There have been some enhancements and fixes (as well as recently discovered
bugs) in the new Win2K OS wordbreaker dll (infosoft.dll). Are you using SQL
7.0 or 2000 on NT 4.0 or Win2K?
I've taken your query and slightly modified it to work with the FT enabled
Northwind table Employees and column Notes (see the below SQL code) and and
you can see that it errors out with Msg 7619 "A clause of the query
contained only ignored words". Note, the message text difference from SQL
7.0, this is a "fix" that is incorported in the MSSearch 2.0 search engine
that ships with SQL 2000. Since numbers are in the noise word file
(noise.enu = English_US), you will need to remove them to get this query to
work or as you've stated previously, you could remove the noise number
before the containstable query is executed.
Another option is to use double quotes "1970~psychology~University~Nancy"
around your search words to create "phrases", noise words or letters are
truly "ignored" within the phrase. Checkout KB Article Q246800 "INF:
Correctly Parsing Quotation Marks in FTS Queries" at
http://support.microsoft.com/support/kb/articles/Q246/8/00.ASP for more
details as well as code examples in JScript and VBScript.
Regards,
John
use Northwind
go
select @@version
go
select @@language
go
-- Query only modified to for FT enabled Northwind table Employees and
column Notes...
select * from containstable(Employees,
Notes,'1997~Donruss~Preferred~Precious~3~Metals~Greg~Maddux')
/*-- Returns:
Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
Aug 6 2000 00:57:48
Copyright (c) 1988-2000 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: )
------------
us_english
Server: Msg 7619, Level 16, State 1, Line 1
A clause of the query contained only ignored words.
*/
select * from containstable(Employees,
Notes,'1970~psychology~University~3~Metals~Nancy')
/*-- Returns:
Server: Msg 7619, Level 16, State 1, Line 1
A clause of the query contained only ignored words.
*/
select * from containstable(Employees,
Notes,'1970~psychology~University~Metals~Nancy')
/*-- Returns:
KEY RANK
----------- -----------
(0 row(s) affected)
*/
select * from containstable(Employees,
Notes,'"1970~psychology~University~Nancy"')
/*-- Returns:
KEY RANK
----------- -----------
(0 row(s) affected)
*/
"Chris Borden" <cbo...@beckett.com> wrote in message
news:OCh6cF3dAHA.1280@tkmsftngp03...