FULL TEXT SEARCH PROBLEM

  • ISSUE 1

    I want to use variable in fulltext funtions while accessed through cursor, from below error it feels like this feature not supported

    CODE SAMPLE

    DECLARE cr_GroupByAddZipNameMatch CURSOR FOR

          --SET @vZip1 = '"' + @vZip1 + '"'

          SELECT * From temp

          where

          freetext(fullname, @vFullName1) and

          freetext(address,@vAddress1) and contains(zip,@vZip1)

    Server: Msg 1079, Level 15, State 1, Line 58

    A variable cannot be used to specify a search condition in a fulltext predicate when accessed through a cursor.

    any work around ?

    ISSUE 2

    getting follwing error when a variable to be used in fulltext funtions has been assinged a value from query, it works when value is assinged explicitly

    CODE:

    SELECT * From temp

    declare @vTemp as varchar(50)

    --set @vTemp = 'Kevin Durban- Jackson'

    set @vTemp = (SELECT FULLNAME FROM TEMP where incontact=46)

    --set @vTemp = '" ' + @vTemp + '" '

    SELECT distinct * From temp

    where

    freetext(fullname, @vTemp) and

    freetext(address,'14 Marchmont Rd') AND  contains(zip,'"SM6 9NU"')

    Server: Msg 7631, Level 15, State 1, Line 10

    Syntax error occurred near '''. Expected '_STRING' in search condition 'Kevin Durban- Jackson

    SAMPLE TABLE SCRIPT:

    CREATE TABLE [dbo].[Temp1] (

         [PriorityCode] [int] NULL ,

         [RecentContactID] [binary] (8) NULL ,

         [Contact_Id] [binary] (8) NOT NULL ,

         [FullName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

         [Gender] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

         [Address] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

         [ZIP] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

         [Phone] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

         [MaxOrderDate] [datetime] NULL

    ) ON [PRIMARY]

    GO

    SAMPLE TABLE DATA:

    1,10 mont Rd

    ,SM7 9LU

    ,Kevin Durban- Jackson

    ,82,Male

        ,

    1,5 mont Rd

    ,SM7 9LU

    ,Kevin Durban - Jackson

    ,42,Male

        ,

    1,2 mont Rd

    ,SM7 9LU

    ,Kevin Durban- Jackson

    ,1,Male      ,

    1,2 mont Rd

    SM7 9LU,Kevin Durban-jackson

    ,3,Male      ,

    5,13 Map Road

    ,SL3 8XZ

    ,Jo Rowden

    ,1,Female

      ,

    5,13 Map Road

    ,SL3 8XZ

    ,Nige Bacon

    ,1,Male      ,

    7,14 Maplin road

    ,SK2 5XD

    ,Viv Kosh

    ,2,Male      ,

    8,3 Mapop Road

    ,NN10 0KO

    ,Kevin Durban-jackson  

    ,1,Female    ,

    thanks in advance

    jadoger

  • you should be able to declare a temp table or table variable and insert the results into that, then use your cursor against the table of results

    eg

    DECLARE @tbl TABLE (..........)

    INSERT INTO @tbl

    SELECT ................

    From temp

    where freetext(fullname, @vFullName1) and

    freetext(address,@vAddress1) and contains(zip,@vZip1)

    DECLARE cr_GroupByAddZipNameMatch CURSOR FOR

    SELECT * FROM @tbl

     

     

     

  • ISSUE 2

    getting follwing error when a variable to be used in fulltext funtions has been assinged a value from query, it works when value is assinged explicitly

    ???????

     

    My table has got more then 2 million records, on top of that I am still not able to use variable in freetext when its been assigned a value from a query

     

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply