FUL 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

SQL Server Development (2000)

FUL TEXT SEARCH PROBLEM

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-09-08 : 09:06:41

Ahsan writes "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