Results 1 to 3 of 3

Thread: SQL Server fulltext - Search failures

  1. #1

    Thread Starter
    Powered By Medtronic dbasnett's Avatar
    Join Date
    Dec 2007
    Location
    Jefferson City, MO
    Posts
    9,754

    SQL Server fulltext - Search failures

    After four years of working without error our full text searches have been returning results that are incorrect. Sometimes we get fewer matches than expected, sometimes more. Back to back searches for the exact same search criteria can give different results. This problem seems to come and go at random times. Mostly it works correctly.

    The full text table contains 30,038 entries and is rarely updated, two records per week would be a lot.

    MSSQL version 11.0.7493.4.

    You can try the search by visiting Missouri Revisor

    Any insights appreciated.

    The query to search for mule. which should return 14 matches.

    Code:
         SET NOCOUNT ON; SELECT [BID] FROM [StatConst].[revision].[SectFullText] WHERE  CONTAINS(body, '"mule" ') and [hist] Is Null
    [StatConst].[revision].[SectFullText] has four columns,

    BID int
    Body nvarchar(MAX)
    hashnum int
    hist int
    Last edited by dbasnett; Nov 22nd, 2021 at 01:45 PM.
    My First Computer -- Documentation Link (RT?M) -- Using the Debugger -- Prime Number Sieve
    Counting Bits -- Subnet Calculator -- UI Guidelines -- >> SerialPort Answer <<

    "Those who use Application.DoEvents have no idea what it does and those who know what it does never use it." John Wein

  2. #2
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,424

    Re: SQL Server fulltext - Search failures

    1) Since you're using double quotes (simple_term = exact match case insensitive), have you tried the LIKE-Operator instead? Maybe lower casing everything before comparing
    Just to test if the results are the same resp. "wonky"
    2) Since "hist" is an int, have you tried hist=0 (or-ed with Is Null)? Maybe someone changed the default-value
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  3. #3

    Thread Starter
    Powered By Medtronic dbasnett's Avatar
    Join Date
    Dec 2007
    Location
    Jefferson City, MO
    Posts
    9,754

    Re: SQL Server fulltext - Search failures

    Quote Originally Posted by Zvoni View Post
    1) Since you're using double quotes (simple_term = exact match case insensitive), have you tried the LIKE-Operator instead? Maybe lower casing everything before comparing
    Just to test if the results are the same resp. "wonky"
    2) Since "hist" is an int, have you tried hist=0 (or-ed with Is Null)? Maybe someone changed the default-value
    If I rebuild and repopulate the problem goes away.
    My First Computer -- Documentation Link (RT?M) -- Using the Debugger -- Prime Number Sieve
    Counting Bits -- Subnet Calculator -- UI Guidelines -- >> SerialPort Answer <<

    "Those who use Application.DoEvents have no idea what it does and those who know what it does never use it." John Wein

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width