numbers in CONTAINSTABLE search

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)

numbers in CONTAINSTABLE search

Author

Topic

MorningZ
Starting Member

44 Posts

Posted - 2004-04-07 : 22:03:05

I cannot find the solution to this on the net, hopefully someone of these boards know

i have a CONTAINSTABLE select going on, but how in the world does one get it to work with a number?

for instance:

DECLARE @Search varchar(50)
SET @Search = '2 AND people AND said'

SELECT RANK, t.Topic, t.Forum, t.Views, t.Subject 
FROM ForumTopic t, CONTAINSTABLE(ForumTopic, Subject, @Search) searchTable 
WHERE [KEY] = t.Topic AND (t.Locked = 0)

that spits out:

quote:

Execution of a full-text operation failed. A clause of the query contained only ignored words.

i took out the "2 AND" part, and it works, so i know thats the problem...

i also tried:
SET @Search = '"2" AND people AND said'

and that gave the same error

is there a solution to this?

thanks in advance

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2004-04-07 : 22:59:22

Welcome to the frustrating world of FTI...

By default all single digits are considered reserved words as well as "said" by the way. Now the good news, then some more bad news

Good News:
There is a file (Noice.eng or Noise.enu - which are the english noise words..) in your FTI configuration data directory (\Mssql\Ftdata\Sqlserver\Config\ or similar) that has a list of all the reserved words in a simple text format.. Make a backup first.. You will need to stop the Microsoft Search service then edit the file/s to remove you reserved words and then start the service back up.

Bad News..
A full rebuild of the FTI is required..

Hope this helps....

DavidM

"If you are not my family or friend, then I will discriminate against you"