[thelist] SQL Server 2000 Full-text search question

[Judah McAuley]

'lo all.  I've got SQL Server 2000 and I've recently set up a table for
Full Text Search.  The table has most of the columns indexed for this
task because I essentially want the front end interface to be able to
search through an entire record for matching data.

My problem is that SQL Server is only searching for terms within a
column and not across columns.  If a record has a company field of "My
House Incorporated" and a description field of "This company has been
building adobe structures since 1976" I want a search for "house AND
adobe" to return this record.  But instead, SQL Server appears to do the
search for "house AND adobe" in each column in the table, but will not
combine data across columns.

I'm running the following query:

SELECT id, last_name, first_name, title, company
FROM directory d INNER JOIN
CONTAINSTABLE(directory, *, ' #search_terms# ')
	AS searchView ON d.id = searchView.[KEY]
WHERE d.active = 1 AND d.remove_date > #Now()# AND d.display_date <= #Now()#
ORDER BY searchView.RANK, d.feature_rank DESC

Any idea how I could make it do what I want rather than what it wants?
My current best thought is to create another column in the table that
aggregates all of the data I want to search over into a single text
field.  I'd like to avoid the resulting database bloat if I can though.

TIA,
Judah