July 13, 2004 at 2:36 am
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
July 13, 2004 at 6:41 am
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
July 13, 2004 at 7:06 am
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