ANSI_NULLS And Null Comparison

Hi All,

I'm converting some stored procs from Sybase to SQL Server, and I'm
having serious problems with Null comparisons.

When I run the code below on Sybase, it returns both rows. When I run
it on MS SQL Server, it returns nothing. I have set ANSI_NULLS off in
the code (and on the session through Query Analyzer), but it doesn't
seem to make any difference. Am I missing something?


---------------
set ANSI_NULLS off

drop table #TestNull

create table #TestNull (Field1 varchar(10), Field2 varchar(10))

insert into #TestNull values (1, null)
insert into #TestNull values (1,1)

declare @TestVar varchar(10)

select @TestVar = Null

select * from #TestNull where Field1 = @TestVar
---------------


Thanks in advance,
Saul