Using FreeText with variables

Question

Post reply

Using FreeText with variables

Amy Cook

SSC Enthusiast

Points: 154
More actions
September 2, 2005 at 10:47 am

#88950

Hi all,
In SQL Server 7 (sp4) I have a table that I have setup for full-text indexing. All of the catalogs are populated and my freetext query works until I try to pass a variable in:
Working Example:
SELECT Title FROM KNOWLEDGE where FREETEXT(title, 'tanks')
This query returns the correct results
Non-working example:
declare @SearchKeywords char(30)
set @SearchKeywords ='tanks'
SELECT Title FROM KNOWLEDGE where FREETEXT(title, @SearchKeywords)
go
This query returns
Server: Msg 170, Level 15, State 1, Line 11
Line 11: Incorrect syntax near '@SearchKeywords'.
I have similar queries on other servers that work with the exact same syntax. I have checked and the database compatibility level is 70.
Any help would be appreciated!

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

the example from BOL clearly shows an example with a variable similar to what youy are doing:

USE pubs

GO

DECLARE @SearchWord varchar(30)

SET @SearchWord ='Moon'

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

does this database have a case sensitive coallation so that the column [Title] is not the same as the search for [title]?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Amy Cook SSC Enthusiast Points: 154 More actions · Answer 2

No, it isn't case sensitive. Also, the query works without the variable. I tried it using [Title] exactly as the column is defined, just to make sure, but no change in the results.

Lowell SSC Guru Points: 323518 More actions · Answer 3

i'm sorry; i would try some lame ideas like changing the definition of @SearchKeywords char(30) to Varchar like in the example; i'll keep looking, but i dno't see what the issue is.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Amy Cook SSC Enthusiast Points: 154 More actions · Answer 4

I've tried it with the varchar as well, I had actually changed it to char just to see if anything else would work. Thanks for your reply. I appreciate your response!

John Kane SSCrazy Points: 2810 More actions · Answer 5

Amy,

In your first reply, you stated that you "...have similar queries on other servers that work with the exact same syntax." So, this syntax error only occurs on one server. Correct? Then most likely this is either a bug in SQL Server 7.0 FTS or possibly related to problems with the FT-enabled text on this server.

Could you post the output of the following SQL code from this problem server?

use <your_database_name_here>

go

SELECT @@language

SELECT @@version

EXEC sp_help_fulltext_catalogs

EXEC sp_help_fulltext_tables

EXEC sp_help_fulltext_columns

EXEC sp_help <your_FT-enable_table_name_here>

go

The query should work on all servers, howver SQL FTS with SQL Server 7.0 has always been *problematic* and if you can, I'd recommend that you upgrade to SQL Server 2000 for many reasons...

Thanks,

John

SQL Full Text Search Blog

http://spaces.msn.com/members/jtkane/

John T. Kane

Amy Cook SSC Enthusiast Points: 154 More actions · Answer 6

It's finally working! I had checked the problem server previously and thought that it had the latest service packs and patches applied, but after your post, I had our DB double check. Apparently it was missing something because after he applied the updates, my FreeText queries are working now. Thanks everyone for your help!