This works for me:
;with table1 (fieldx) as (select 'smith')
,table2 (fieldx) as (select 'jones')
where fieldx not in ( select fieldx from table2 )
No global setting. I assume you've confirmed 'smith' is not in table2?
select * from table2 where fieldx = 'smith'
Is the collation case insesitive? does ether table's smith value contain leading or trailing whitespace?
Be One with the Optimizer