Search notes:

SQL Server: set ansi_nulls

Get current value for ansi_nulls: 
select case when @@options & power(2,  5) > 0 then 'yes' else 'no' end;

Stored procedures

When a stored procedure is created, the currently set value of ansi_nulls is stored with the procedure. When the procedure is executed, it runs with this value (rather than the one that is active when the procedure is executed).
The same applies to the setting quoted_identifier as well.

Heterogenous queries

Heterogenous queries require the setting of ansi_nulls (as well as that of ansi_warnings) to be set to on.

See also

The T-SQL set statement

Index