Fulltext Question

Question

Fulltext Question

Frank Kalis

SSC Guru

Points: 111183
More actions
May 27, 2003 at 4:56 am

#82193

Hi all,
the following is an adaption of a BOL example I've found while searching for CONTAINS.
USE fai
GO
DECLARE @SearchWord varchar(30)
SET @SearchWord ='Hannover'
SELECT MsgSubject FROM mails_header WHERE CONTAINS(MsgNoteText, @SearchWord)
Can anyone explain why this doesn't work in QA.
It comes up with
Server: Msg 170, Level 15, State 1, Line 3
Zeile 3: Wrong syntax ... '@SearchWord'.
If I replace the variable with a string it works as it should
USE fai
GO
DECLARE @SearchWord varchar(30)
SET @SearchWord ='Hannover'
SELECT MsgSubject FROM mails_header WHERE CONTAINS(MsgNoteText, 'Hannover')
And on top a snippet of an asp page
SQL = "SELECT MsgSubject AS Nachricht FROM mails_header WHERE CONTAINS(MsgNoteText, '" & Trim(Request("Suchbegriff")) & "') ORDER BY MsgDateReceived DESC"
rst.Open SQL, cnn
which also works fine.
Any ideas?
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
May 30, 2003 at 8:00 am

This was removed by the editor as SPAM

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

mimorr SSCarpal Tunnel Points: 4850 More actions · Answer 1

My understanding is that you must use the select statement in a string and then execute the string. So your code would look like this:

USE fai

Go

DECLARE @SearchWord varchar(30)

DECLARE @cStr varchar(400)

SET @SearchWord = 'Hannover'

SET @cStr = 'SELECT MsgSubject FROM mails_header WHERE

CONTAINS(MsgNoteText,'''+ @SearchWord+''')'

--print @cstr

exec(@cStr)

You can see what the sting actually looks like with print @cStr if needed.

Michelle

Frank Kalis SSC Guru Points: 111183 More actions · Answer 2

Hi mimorr,

what a great work, and how simple. Finally this has been solved. I've also asked this question on a list where a couple of SQL Server MVP hang around and didn't get a solution.

Thanks!

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Antares686 SSC Guru Points: 125444 More actions · Answer 3

Actually the part about wrapping in a dynamic string is not correct. I just tried your example code from the beginning subing my table, column and a valid value and have no issues. Are you sure you didn't have a typo in your try that didn't get posted here?

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

Hi Antares686,

quote:
Actually the part about wrapping in a dynamic string is not correct. I just tried your example code from the beginning subing my table, column and a valid value and have no issues. Are you sure you didn't have a typo in your try that didn't get posted here?

sorry, there is no typo. I've found this somewhere in BOL while looking for fulltext information and changed it for me.

The snippet from BOL is

USE pubs

GO

DECLARE @SearchWord varchar(30)

SET @SearchWord ='Moon'

SELECT pr_info FROM pub_info WHERE CONTAINS(pr_info, @SearchWord)

You'll find it while searching for CONTAINS at the bottom of that page.

I've looked times and times again for typos, but either I am blind or there isn't a typo?

However, finally it works!

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Antares686 SSC Guru Points: 125444 More actions · Answer 5

Odd, I have run the example and never had an issue.