ANSI_NULLS OFF SQL2K5

Hi folks,
I'm using SQL Server 2005 SP1 and wants to know how to set to OFF the
setting ANSI_NULLS. When I create or modify a SP, SQL Server 2005 proposes
automatically the settings (global)
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
When I modify the SP cliccking on "Script store procedure as" and then
"ALTER TO" to change the setting ANSI_NULLS to OFF, it remains in OFF. The
problem is when I modify the SP cliccking on "Modify"; if the SP was modifie
d
first changing the ANSI_NULLS setting to OFF, SSMS continues to visualize
such setting in ON. Very weird!
Any insight about how to preserve such setting in OFF is really welcome.
Thanks. Roberto.BOBNET wrote:
> Hi folks,
> I'm using SQL Server 2005 SP1 and wants to know how to set to OFF the
> setting ANSI_NULLS. When I create or modify a SP, SQL Server 2005 proposes
> automatically the settings (global)
> SET ANSI_NULLS ON
> SET QUOTED_IDENTIFIER ON
> When I modify the SP cliccking on "Script store procedure as" and then
> "ALTER TO" to change the setting ANSI_NULLS to OFF, it remains in OFF. The
> problem is when I modify the SP cliccking on "Modify"; if the SP was modif
ied
> first changing the ANSI_NULLS setting to OFF, SSMS continues to visualize
> such setting in ON. Very weird!
> Any insight about how to preserve such setting in OFF is really welcome.
> Thanks. Roberto.
If at all possible you should avoid setting ANSI_NULLS OFF. Use the ON
setting always. The only good reason I can think of to use the OFF
setting is in order to support legacy code that you can't change. If
you do use the OFF setting then some features are not supported and
worst of all your code will be harder for others to comprehend.
Set ANSI_NULLS in the script just before the CREATE PROC statement:
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[usp_proc]
...
You can also set Management Studio's default behaviour under Options /
Query Execution\SQL Server\ANSI. Changing this to OFF is definitely not
recommended because it will affect ALL new procs unless you remember to
specify otherwise.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--