FULL TEXT SEARCH PROBLEM

[jadoger]

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

 

[mrdenny]

For issue number 1 I would recommend doing your full text search, and putting the results into a temp table, then query from the main table using the temp table as your filter for the cursor.

I can't help with number 2. I'm not sure what's going on there.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[jadoger]

Much thanks for the advice of using temp table I have recived the same advice from other forums i have posted the message.

ISSUE 2, well, all what I want to do is assign a value to a varible from a select query and then use that variable in freetext() or contains(), when I assign value explicitly it works but not when I assign a value to a variable from query e.g.

IT WORKS !

Declare @vTemp varchar(50)
SET @vTemp = '"Nash-Oblien"'
SELECT distinct * From temp
where
freetext(fullname, @vTemp) and
freetext(address,'14 Marchmont Rd') AND contains(zip,'"SM6 9NU"')


DONT WORK!

Declare @vTemp varchar(50)
SET @vTemp = (Select TOP 1 Name FROM NameTable)
SELECT distinct * From temp
where
freetext(fullname, @vTemp) and
freetext(address,'14 Marchmont Rd') AND contains(zip,'"SM6 9NU"')



it gives me error, can any one help regarding this ?

jadoger

 

[mrdenny]

Instead of set @vTemp = (select top1 ...) have you tried

select @vTemp = Name
from TempTable

No idea if that would help or why it might help, but you never know.

Denny

--Anything is possible. All it takes is a little research. (Me)