RE: FULL TEXT SEARCH

From: Ahsan (Ahsan_at_discussions.microsoft.com)
Date: 07/16/04 

Date: Fri, 16 Jul 2004 01:36:01 -0700

John,
data type and length are same but I am still getting this error, I am using Sql server 2000 standard edition on windows 2000
ERROR IS:
Server: Msg 7631, Level 15, State 1, Line 19
Syntax error occurred near '''. Expected '_STRING' in search condition 'Kevin Durban- Jackson
'.

SELECT * From temp
QUERY:
SET QUOTED_IDENTIFIER OFF
declare @vTemp as varchar(50)

SET @vTemp = (SELECT ltrim(rtrim(FULLNAME)) FROM TEMP where incontact=46)
--Set @vTemp = '"' + @vTemp + '"'
--print @vTemp
SELECT distinct * From temp
where
freetext(fullname,@vTemp)
and freetext(address,'14 Marchmont Rd') AND contains(zip,'"SM6 9NU"')

TABLE SCRIPT:
CREATE TABLE [dbo].[Temp] (
        [ID] [int] NOT NULL ,
        [Address] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
        [ZIP] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
        [FullName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
        [InContact] [numeric](18, 0) NULL ,
        [Gender] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
        [RecentID] [int] NULL
) ON [PRIMARY]
GO

SAMPLE DATA:
(this is exported from sql server export facility)
1,14 Marchmont Rd
,SM6 9NU
,Kevin Durban- Jackson
,46,Male
    ,
2,14 Marchmont Road
,SM6 9NU
,Kevin Durban - Jackson
,42,Male
    ,
3,14 Marchmont Road
,SM6 9NU
,Kevin Durban- Jackson
,1,Male ,
4,14 Marchmont Rd
,SM6 9NU,Kevin Durban-jackson
,3,Male ,
5,14 Maplin Park
,SL3 8XY
,Jo Rowden
,1,Female
  ,
6,14 Maplin Park
,SL3 8XY
,Nige Bacon
,1,Male ,
7,14 Maplin Drive
,SK2 5XJ
,Viv Nash

,2,Male ,
8,14 Maple Wood
,NN10 0UN
,Kevin Durban-jackson

,1,Female ,

Could you give it a try by creating the sample table in your sql server john?
many thanks in advance

"John Kane" wrote:

> Ahsan,
> Since this is the fulltext newsgroup, I thought I'd provide an example of a successful variable assignment for your question:
>
> declare @keyword varchar(100), @count int
> set @keyword = 'MyActivityList'
> set @count = (select count(*) from jtkane_ASP_Procs where contains(*,@keyword))
> print @count -- returns: 1
>
> A non-FTS solution using TOP 1 <valid_column_name> example:
>
> declare @SearchWord varchar(100)
> SET @SearchWord = (select top 1 ASPFile from jtkane_ASP_Procs)
> print @SearchWord
> -- returns: MyActivityList.cs
>
> In your example below your variable @SearchWord should be declared as the same datatype and lenght as the column named: Fields1 from your table named: TableName.
>
> Hope this helps.
> Regards,
> John
>
>
> "Ahsan" wrote:
>
> > Microsoft Says:
> > This example uses a variable instead of a specific search term.
> >
> > USE pubs
> > GO
> > DECLARE @SearchWord varchar(30)
> > SET @SearchWord ='Moon'
> > SELECT pr_info FROM pub_info WHERE FREETEXT(pr_info, @SearchWord)
> >
> >
> >
> > at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_fa-fz_2juc.asp
> >
> > but what I want is to assign a value to the variable @SearchWord from a query like
> >
> > SET @SearchWord = (SELECT top 1 Fields1 from TableName)
> >
> > which seems to be not working and given a error.
> > Server: Msg 7631, Level 15, State 1, Line 15
> > Syntax error occurred near '''. Expected '_STRING' in search condition 'Some Value
> > '.
> >
> > where 'Some value' will be the value selected by the query 'SELECT top 1 Fields1 from TableName', so can any one help me out that how to use a variable which has been asigned a value from a query not explicitly assigned like SET @SearchWord ='Moon' ?, I have tried every combination of wraping the variable with single and double qoutes but its not working
> >
> > many thanks
> >
> >
> >

Relevant Pages

  • Re: Error re-installing SQL Server 2005
    ... Kevin Struckhoff ... > decided to try out sql server 2008 rc0. ... Refer to server error logs and setup logs for ... For details on how to view setup logs, ...
    (microsoft.public.sqlserver.setup)
  • Re: TOP in query user selects amount to return
    ... if someone is using MS SQL Server 2005 instead of Jet ... One method that can be used is to use a ranking query to assign a number ... "Kevin" wrote: ...
    (microsoft.public.access.queries)
  • RE: Understanding how my code is causing deadlocks!
    ... "Kevin Kraus" wrote: ... > SQL Server and sending it to a web service. ... > - First update statement and select statement are sent to SQL server in one ... > My understanding of deadlocks is that my process thread needs to have a lock ...
    (microsoft.public.sqlserver.server)
  • Re: Server no longer on list
    ... Thanks Kevin. ... Anyone hear of some component of windows update that installs SQL server on ... When I click on the connection part of my adp, ...
    (microsoft.public.access.adp.sqlserver)