FULL TEXT SEARCH PROBLEM - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

FULL TEXT SEARCH PROBLEM

Author

Topic

jadoger
Starting Member

13 Posts

Posted - 2004-07-13 : 04:41:21

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources