September 1, 2005 at 10:54 am
Here's the query I attempted to execute:
SELECT pb.ProductID, pb.SalesText, pb.VendorID, pb.VendorName, pb.VariantID, pb.CatalogName, pb.QtyItemMultiples,PricePage = '' FROM SyngentaFlowers_Commerce.dbo.Plant_brok_catalogproducts pb WHERE COALESCE(pb.OverrideDisplayFlag, pb.DisplayFlag, 1) = 1 AND COALESCE(pb.VarOverrideDisplayFlag, pb.VarDisplayFlag, 1) = 1 AND pb.i_classType = 2 AND CONTAINS(pb.SalesText, 'A*') ORDER BY SalesText, VendorName
and got the following message from the SQL Query Analyzer:
Server: Msg 7619, Level 16, State 1, Line 1
Execution of a full-text operation failed. A clause of the query contained only ignored words.
In case anyone wants to know:
SELECT @@version:
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
*Note: I installed SP4 yesterday, but it doesn't show up here. Why is this?
Thanks in advance.
September 1, 2005 at 11:19 am
Note: I installed SP4 yesterday, but it doesn't show up here.
Yes it does...
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
2039 = SQL Server 2000 SP4
(Build 3790: Service Pack 1) = this applies to the operating system only.
-SQLBill
September 1, 2005 at 11:24 am
As for your error message....
Searching the BOL under "full-text queries-overview", I found that words like "a", "the", "and" are IGNORED. So, it might be that a part of the where clause is only finding these types of words.
Search for this file: noise
The actual file name will end with the language abbreviation (US English would be noise.enu). The file should be in: \mssql\ftdata\sqlserver\config
-SQLBIll
September 1, 2005 at 11:27 am
1) Why aren't you using
AND pb.SalesText like 'A%'
instead of
CONTAINS(pb.SalesText, 'A*') ORDER BY SalesText, VendorName
No need for the fulltext indexing and uses a regular index instead.
2) SP4 probably has some bugfixes voor mssearch.
Like when japanese characters are found
http://support.microsoft.com/?kbid=892924
3) see the books online on how to use CONTAINS
CONTAINS(pb.SalesText, 'A*')
looks literally for 'A*' and will ignore 'ABC' and such. -> Word is probably too short (min 3 characters would be plausible)
Remark: use double quotes
Snippet:
The clause should be specified this way: CONTAINS (column, '"text*"')
The asterisk matches zero, one, or more characters (of the root word or words in the word or phrase). If the text and asterisk are not delimited by double quotation marks, as in CONTAINS (column, 'text*')
, full-text search considers the asterisk as a character and will search for exact matches to text*
.
I haven't used full text indexing yet, so I'm happy to learn from your future questions.
September 1, 2005 at 11:52 am
Ignored words could be just part of the problem because FULLTEXT is Microsoft Proprietry feature you have to use FREETEXT, FREETEXTTABLE, CONTAINS and CONTAINSTABLE without functions like COALESCE. I would check the BOL (books online) and MSDN for sample code. Hope this helps.
Kind regards,
Gift Peddie
Kind regards,
Gift Peddie
September 1, 2005 at 12:40 pm
Thanks for all the help!
I decided to just put a CustomValidator object in my web page, and check for the length whether it was less than 3 characters. Saved me from a lot of frusturation.
Anyways, thanks a lot for all of the information. I'll definitely benefit from this.
September 2, 2005 at 7:15 am
The minimum character size is 2 characters, however, if those two characters are a reserved word (such as "to" or "as"), as found in the noise.enu file, it will give the same error. For 3 characters, it's the same situation. If the user were to enter "the" or "and", those words would cause the error as well.
September 2, 2005 at 9:59 am
I forgot about words like "the" or "and".
Is there a programmatic way to reference noise.enu using ADO.NET and C#?
Thanks in advance.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply