MS SQL Server - Sql Server Resetting Ansi

Author

Sql Server Resetting Ansi_Nulls Setting

DonH

2006-03-14, 1:23 pm

In trying to resolve an issue with nulls being treated diferent in sql
server 2005 than prior version, I have change the settings when
creating stored proc to specify SET ANSI_NULLS OFF.

The problem that I have is that when I try to modify the stored proc,
SQL Server will generate the script with the Setting SET ANSI_NULLS ON.
I have tried to change the database options and just about everything
I can find, but I cannot seem to get Sql Server to remember this
setting.

How can I default this so that when I get the stored proc script, this
setting is the way I left it?

David Portas

2006-03-14, 8:23 pm

DonH wrote:
> In trying to resolve an issue with nulls being treated diferent in sql
> server 2005 than prior version, I have change the settings when
> creating stored proc to specify SET ANSI_NULLS OFF.
>
> The problem that I have is that when I try to modify the stored proc,
> SQL Server will generate the script with the Setting SET ANSI_NULLS ON.
> I have tried to change the database options and just about everything
> I can find, but I cannot seem to get Sql Server to remember this
> setting.
>
> How can I default this so that when I get the stored proc script, this
> setting is the way I left it?

Why do you want to turn ANSI_NULLS OFF? This is very non-standard and
certainly highly undesirable if you expect other people to be able to
understand and support your code. Also, setting ANSI_NULLS OFF disables
or cripples some database engine features. ANSI_NULLS ON has been the
default in SQL Server connections for nearly 10 years now.

Unfortunately there was a nasty "feature" in SQL Server 2000, whereby
Enterprise Manager used the OFF setting as the installation default
when you created a new proc through that interface. Query Analyzer
wasn't affected. Although the EM setting was configurable, those unwise
enough to create procs through EM probably didn't realise it was even
there. In 2005 Management Studio, new procs are created from a template
which is user-editable.

Use SET ANSI NULLS ON with all your CREATE PROCs. If you have legacy
code that relies on the non-standard setting then now ought to be a
good time to fix it.

--
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
--

DonH

2006-03-15, 9:23 am

The problem that I have is legacy stored procedures that may try to
join to a field that may contain a null value and not returning the
correct results. I also have over 600 stored procedures, some that are
very complicated that I would have to go through and correct. While I
can appreciate that this may not be the "correct" way of addressing
this issue, it is at the moment about the only choice that I have. We
are under a tight budget and deadline to complete development and I do
not have the resources or time to go through all of these stored
procedures. I was looking for a stop gaap measure that would allow me
to continue development and have the procedures return the expected
result.

Is there a way to change a setting so that when I retrieve the script
from Sql Server, that option will be set to off so that the developers
do not have to remember to make the change manually every time a script
gets modified?

Sponsored Links