Hi Guys

SQL Server 2000 SP3
(WIN NT/2000/XP)

Can somebody please explain how I can overcome the
following (what looks to be) limitation in SQL Server's
FullText capabilities?

Consider the following query (table defs at bottom of
post):

select *
from a
left outer join b on b.b_id = a.b_id

Which returns:

a_id b_id b_id foo
---- ---- ---- ---
1    NULL NULL NULL
2    1    1    bar

Now, the column foo in table b is fulltext indexed.
So, the following query returns the following, correct
results:

select *
from a
left outer join b on b.b_id = a.b_id
where contains(b.*, '"bar"')

a_id b_id b_id foo
2    1    1    bar

However, if I want to return all of the rows that do not
contain the word "bar", I would execute the following:

select *
from a
left outer join b on b.b_id = a.b_id
where not contains(b.*, '"bar"')

But, this query does not return *any* rows. I was
expecting to see:

a_id b_id b_id foo
---- ---- ---- ---
1    NULL NULL NULL

It is as if my query has become an INNER JOIN, i.e. it
yields the same results as:

select *
from a
inner join b on b.b_id = a.b_id
where not contains(b.*, '"bar"')

...which I would expect.

Oddly, SET ANSI_NULLS OFF fixes the problem - but I
cannot see why this relates to CONTAINS searching and
besides. SET ANSI_NULLS OFF is definitely *not* an option
for me.

My questions are:

1. Is this the expected behaviour?
2. Is this a common problem?
3. What can I do to "fix" the problem?

Incidentally, executing the same NOT CONTAINS query in
SQL Server 2005 BETA2 does *not* exhibit the problem,
i.e. it works as we would like it to.

Many Thanks in advance.

create table a(a_id int not null constraint pk_a primary
key, b_id int)
insert into a values(1, null)
insert into a values(2, 1)

create table b(b_id int not null constraint pk_b primary
key, foo varchar(100))
insert into b values(1, 'bar')

sp_fulltext_catalog 'test', 'create'
sp_fulltext_table 'b', 'create', 'test', 'pk_b'
sp_fulltext_column 'b', 'foo', 'add'
sp_fulltext_table 'b', 'activate'
sp_fulltext_table 'b', 'start_full'