Post reply

Ignored words in SQL

  • September 1, 2005 at 10:54 am

    #165837

    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

    #586945

    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

    #586949

    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

    #586951

    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

    #586969

    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

    #586982

    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

    #587174

    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

    #587306

    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