All it takes is one noise word to ruin query!

The presence of a single noise word is causing query to fail with this
error: "Execution of a full-text operation failed. A clause of the query
contained only ignored words." But the full text clause contains three
words and only one of them is a noise word. Please see below. Is this a
known problem?
Thanks
Timo
QUERY ONE EXCERPT:
This succeeds:
declare @.keywordclause varchar(2000)
set @.keywordclause='FORMSOF(INFLECTIONAL,disease) AND
FORMSOF(INFLECTIONAL,risk)'
...
{select statement}
where contains(mycolumn,@.keywordclause) or contains(myothercolumn,
@.keywordclause)
QUERY TWO EXCERPT
This fails:
declare @.keywordclause varchar(2000)
set @.keywordclause= 'FORMSOF(INFLECTIONAL,disease) AND
FORMSOF(INFLECTIONAL,risk) AND FORMSOF(INFLECTIONAL,your)'
...
where contains(mycolumn, @.keywordclause) or contains(myothercolumn,
@.keywordclause)
Timo,
Unfortunately, yes this is a well known and a "by design" issue. There are
several workarounds, one is to remove all noise words (except for a single
space) from your language-specific noise file (US English = noise.enu) and
run a full population. Another is to encapsulate your search strings in
double quotes as this will allow the noise word to be truly ignored. For
more information on this see, SQL 2000 BOL topic "Full-text Search
Recommendations" and KB article 246800 (Q246800) "INF: Correctly Parsing
Quotation Marks in FTS Queries" at
http://support.microsoft.com//defaul...b;EN-US;246800 for more
details.
Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Timo" <Timo@.unspam.biz> wrote in message
news:#hK$w7EcFHA.1568@.TK2MSFTNGP10.phx.gbl...
> The presence of a single noise word is causing query to fail with this
> error: "Execution of a full-text operation failed. A clause of the query
> contained only ignored words." But the full text clause contains three
> words and only one of them is a noise word. Please see below. Is this a
> known problem?
> Thanks
> Timo
>
> QUERY ONE EXCERPT:
> This succeeds:
> declare @.keywordclause varchar(2000)
> set @.keywordclause='FORMSOF(INFLECTIONAL,disease) AND
> FORMSOF(INFLECTIONAL,risk)'
> ...
> {select statement}
> where contains(mycolumn,@.keywordclause) or contains(myothercolumn,
> @.keywordclause)
> QUERY TWO EXCERPT
> This fails:
> declare @.keywordclause varchar(2000)
> set @.keywordclause= 'FORMSOF(INFLECTIONAL,disease) AND
> FORMSOF(INFLECTIONAL,risk) AND FORMSOF(INFLECTIONAL,your)'
> ...
> where contains(mycolumn, @.keywordclause) or contains(myothercolumn,
> @.keywordclause)
>
|||Thanks for the reply, John, but I don't understand yet. Perhaps I'm
misreading the KB article, or it could be that the KB article does not
reflect the MSFT code base; it reads:
"Notice that proximity, generation, and weighted terms do not use double
quotation marks."
Here's the algorithm excerpted verbatim from the article:
Replace all double quotes (clears the text and any improper quotations)
If the text string contains one of the key words "NEAR", "FORMSOF", or
"ISABOUT", the parsing is complete
Else
Surround any instances of 'and' or 'and not' with quotes
Surround any instances of 'or' or 'or not' with quotes
Surround the entire string with quotesI'm using the FORMSOF generation
function:
declare @.keywordclause varchar(2000)
set @.keywordclause='FORMSOF(INFLECTIONAL,disease) AND
FORMSOF(INFLECTIONAL,risk)'
So I *shouldn't* be using double-quotation marks, right?
My workaround has been to create my own noisewords table and eliminate from
@.keywordclause any term that appears in my noisewords table.
Regards
Timo
"John Kane" <jt-kane@.comcast.net> wrote in message
news:#qdAANIcFHA.2768@.tk2msftngp13.phx.gbl...
> Timo,
> Unfortunately, yes this is a well known and a "by design" issue. There are
> several workarounds, one is to remove all noise words (except for a single
> space) from your language-specific noise file (US English = noise.enu) and
> run a full population. Another is to encapsulate your search strings in
> double quotes as this will allow the noise word to be truly ignored. For
> more information on this see, SQL 2000 BOL topic "Full-text Search
> Recommendations" and KB article 246800 (Q246800) "INF: Correctly Parsing
> Quotation Marks in FTS Queries" at
> http://support.microsoft.com//defaul...b;EN-US;246800 for more
> details.
> Thanks,
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "Timo" <Timo@.unspam.biz> wrote in message
> news:#hK$w7EcFHA.1568@.TK2MSFTNGP10.phx.gbl...
>
|||Timo,
Both single and double quotes are useful depending upon your requirements.
As for double quotes, I was referring to the following in "Full-text Search
Recommendations":
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, ...
Specifically, note the different results from the above re-written query:
select * from pub_info where CONTAINS(pr_info, 'between AND king')
-- returns: Execution of a full-text operation failed. A clause of the query
contained only ignored words.
select * from pub_info where CONTAINS(pr_info, '"between AND king"')
-- returns: 0 rows as king is not in the pub_info table, and no error.
Your solution also works as there are many means to the same solution!
Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Timo" <Timo@.spamspamspam.org> wrote in message
news:#q2sV2McFHA.720@.TK2MSFTNGP15.phx.gbl...
> Thanks for the reply, John, but I don't understand yet. Perhaps I'm
> misreading the KB article, or it could be that the KB article does not
> reflect the MSFT code base; it reads:
> "Notice that proximity, generation, and weighted terms do not use double
> quotation marks."
> Here's the algorithm excerpted verbatim from the article:
> Replace all double quotes (clears the text and any improper quotations)
> If the text string contains one of the key words "NEAR", "FORMSOF", or
> "ISABOUT", the parsing is complete
> Else
> Surround any instances of 'and' or 'and not' with quotes
> Surround any instances of 'or' or 'or not' with quotes
> Surround the entire string with quotesI'm using the FORMSOF
generation
> function:
> declare @.keywordclause varchar(2000)
> set @.keywordclause='FORMSOF(INFLECTIONAL,disease) AND
> FORMSOF(INFLECTIONAL,risk)'
> So I *shouldn't* be using double-quotation marks, right?
> My workaround has been to create my own noisewords table and eliminate
from[vbcol=seagreen]
> @.keywordclause any term that appears in my noisewords table.
> Regards
> Timo
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:#qdAANIcFHA.2768@.tk2msftngp13.phx.gbl...
are[vbcol=seagreen]
single[vbcol=seagreen]
and[vbcol=seagreen]
query[vbcol=seagreen]
three[vbcol=seagreen]
a
>
|||you might want to check out
http://www.indexserverfaq.com/noise.htm
The forms of inflectional search is functionally equivalent to a freetext
search. You might get what you are looking for using FreeText search and
this query won't bomb if a noise word is in your search condition.
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
"Timo" <Timo@.unspam.biz> wrote in message
news:%23hK$w7EcFHA.1568@.TK2MSFTNGP10.phx.gbl...
> The presence of a single noise word is causing query to fail with this
> error: "Execution of a full-text operation failed. A clause of the query
> contained only ignored words." But the full text clause contains three
> words and only one of them is a noise word. Please see below. Is this a
> known problem?
> Thanks
> Timo
>
> QUERY ONE EXCERPT:
> This succeeds:
> declare @.keywordclause varchar(2000)
> set @.keywordclause='FORMSOF(INFLECTIONAL,disease) AND
> FORMSOF(INFLECTIONAL,risk)'
> ...
> {select statement}
> where contains(mycolumn,@.keywordclause) or contains(myothercolumn,
> @.keywordclause)
> QUERY TWO EXCERPT
> This fails:
> declare @.keywordclause varchar(2000)
> set @.keywordclause= 'FORMSOF(INFLECTIONAL,disease) AND
> FORMSOF(INFLECTIONAL,risk) AND FORMSOF(INFLECTIONAL,your)'
> ...
> where contains(mycolumn, @.keywordclause) or contains(myothercolumn,
> @.keywordclause)
>
|||Thanks for the link, Hilary. We've opted to strip the noiswords out of the
search argument using TSQL with own noisewords table. So far, the parsing
doesn't seem to be taxing the CPU. Perhaps the page you sent me to could add
a YMMV disclaimer instead of panning this approach in such absolute terms
;-)
Timo
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ejECfeQcFHA.228@.TK2MSFTNGP12.phx.gbl...
> you might want to check out
> http://www.indexserverfaq.com/noise.htm
> The forms of inflectional search is functionally equivalent to a freetext
> search. You might get what you are looking for using FreeText search and
> this query won't bomb if a noise word is in your search condition.
> --
> 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
|||Your point is noted! Its not an application breaker, but you will get
better performance by doing this client side.
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
"Timo" <Timo@.spamspamspam.org> wrote in message
news:O1IZpgacFHA.1152@.tk2msftngp13.phx.gbl...
> Thanks for the link, Hilary. We've opted to strip the noiswords out of
> the
> search argument using TSQL with own noisewords table. So far, the parsing
> doesn't seem to be taxing the CPU. Perhaps the page you sent me to could
> add
> a YMMV disclaimer instead of panning this approach in such absolute terms
> ;-)
> Timo
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:ejECfeQcFHA.228@.TK2MSFTNGP12.phx.gbl...
>