Ansi Nulls and quoted identified

[Raziq Shekha]

Hello everyone,

SQL Server 2000. I have a database in which there are several objects
which have ansi nuls and quoted identifier turned ON. Is there a way I
can generate a script which:
(1) Can identify all objects within the database that have those two
properties turned ON and
(2) Change the properties for these objects and turn the ansi nulls and
quoted identifier OFF for those objects.

I am trying to avoid going throuh gazillions of objects and manually
doing this.

Thanks for any help.

Raziq.



*** Sent via Developersdex http://www.developersdex.com ***

[David Portas]

Re: Ansi Nulls and quoted identified

Are you sure you want to do this? It is STRONGLY recommended that both
these options always be set ON. Changing these options will break
indexed views and other code that uses them. Only use the OFF setting
for legacy code where you don't have another option.

These settings are persisted with views, procs, etc when you create
them. I think the easiest way to change them is to use Enterprise
Manager to script the database, then search and replace in the
resulting script.

--
David Portas
SQL Server MVP
--

[Razvan Socol]

Re: Ansi Nulls and quoted identified

To identify the objects, use something like this:

SELECT name, xtype,
OBJECTPROPERTY( id,'ExecIsAnsiN ullsOn'),
OBJECTPROPERTY( id,'ExecIsQuote dIdentOn')
FROM sysobjects WHERE xtype IN ('P','TR','V',' IF','FN','TF')

To change these properties, you should re-create those objects using
ALTER PROCEDURE / ALTER VIEW / ALTER FUNCTION.

If you do not want to do this one by one (and if you feel particularly
lucky today, as to change an undocumented column in a system table), I
will give you a hint: bits 30 and 29 (bit mask: 0x60000000) of the
status column in the sysobjects table.

Of course, changing a system table is a bad idea (it may cause data
loss or may prevent an instance of SQL Server from running). Changing
an undocumented column of a system table is an even worse idea !

Razvan

[David Portas]

Re: Ansi Nulls and quoted identified

If you do not want to do this one by one and if you feel particularly
SUICIDAL today...

Razvan, have you actually tested out your implied suggestion? I would
NEVER recommend anyone to attempt to modify system tables directly. In
this case it is completely unnecessary.

--
David Portas
SQL Server MVP
--

[Razvan Socol]

Re: Ansi Nulls and quoted identified

Yes, I have tested it (to turn ON those properties) and found no
side-effects.

Razvan

[Razvan Socol]

Re: Ansi Nulls and quoted identified

Correction: I have found a side-effect:

If the procedure was executed (so the execution plan is in the
procedure cache), the change does not take effect immediately (i.e. the
old execution plan remains in the cache, until the server is restarted
or DBCC FREEPROCCACHE is executed).

However, I do not recommend this method. It is undocumented and,
therefore, not reliable. You should script the objects using Enterprise
Manager and use ALTER PROCEDURE / VIEW / FUNCTION.

Razvan

[--CELKO--]

Re: Ansi Nulls and quoted identified

>> turn the ansi nulls and quoted identifier OFF for those objects. <<

Why do you wish to destroy portability and data integrity? The only
reasons I can imagine are (1) ignorance of SQL (2) deliberate
sabotage. You ought ot be bringin the database into Standards
confromance, not out of it!!

[Raziq Shekha]

Re: Ansi Nulls and quoted identified

The application I am working with is designed to work with objects with
those two options turned off.

Raziq.


*** Sent via Developersdex http://www.developersdex.com ***

[--CELKO--]

Re: Ansi Nulls and quoted identified

>> The application I am working with is designed to work with objects with those two options turned off. <<

This is like saying that your accounting package believes that 2 + 2 =
5 so you want to change the spreadsheets and the rest of the world.

You need to re-write your code immediately, don't you? Then you need
a data audit to find out what is messed up in the Database, all of your
reports, etc.

[Raziq Shekha]

Re: Ansi Nulls and quoted identified

Right. Why don't I rewrite the application code, and then when the
application starts acting up, and we have to call the vendor, and they
say: "sorry, but this is not our code and so we can't help you." I can
tell them no problem, because I have been working on reinventing the
wheel and will be making a killing selling that new wheel and won't need
this DBA job.

One more thing, I don't think leaving the above mentioned options off
amounts to a logical inconsistency i.e. 2+2=5.




*** Sent via Developersdex http://www.developersdex.com ***

[Erland Sommarskog]

Re: Ansi Nulls and quoted identified

Razvan Socol (rsocol@gmail.c om) writes:[color=blue]
> Yes, I have tested it (to turn ON those properties) and found no
> side-effects.[/color]

Hm, if you have a procedure that reads:

CREATE PROCEDURE ill_will AS
PRINT "Hej!"

and it was loaded with QUOTED_IDENTIFI ER off, and you change it to ON,
I bet you see have side effects!


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Erland Sommarskog]

Re: Ansi Nulls and quoted identified

Raziq Shekha (raziq_shekha@a nadarko.com) writes:[color=blue]
> One more thing, I don't think leaving the above mentioned options off
> amounts to a logical inconsistency i.e. 2+2=5.[/color]

Oh, you don't know Joe Celko I see. In his world, 2+2=5, running with
ANSI_NULLS off or calling a table row for a record is all equally
wrong.

Then again, what could you expect from someone who thinks that IDENTITY
in SQL Server has something to do with physical location?

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[--CELKO--]

Re: Ansi Nulls and quoted identified

>> Why don't I rewrite the application code, and then when the application starts acting up, and we have to call the vendor, and they say: "sorry, but this is not our code and so we can't help you."<<

It is already acting up and you need to replace it ASAP. Read a nice
article at CIO magazine's website entitled "Bound to Fail" about the
falure of a legacy system that everyone could see and nobody wanted to
change.

If you are in the United States, where we have 1 lawyer for every 400
people, you should be suing this vendor.
[color=blue][color=green]
>> I don't think leaving the above mentioned options off amounts to a logical inconsistency i.e. 2+2=5.<<[/color][/color]

I guess that you are smarter than ANSI, smarter than ISO, smarter than
any other SQL product staff, etc. Do you exchange data with other SQL
products? Do you move data from one table to another within the system
itself? Do you plan on doing these things later? Do you want to have
a Data Warehouse someday?

[Erland Sommarskog]

Re: Ansi Nulls and quoted identified

--CELKO-- (jcelko212@eart hlink.net) writes:[color=blue]
> It is already acting up and you need to replace it ASAP. Read a nice
> article at CIO magazine's website entitled "Bound to Fail" about the
> falure of a legacy system that everyone could see and nobody wanted to
> change.
>
> If you are in the United States, where we have 1 lawyer for every 400
> people, you should be suing this vendor.[/color]

So the system I work with has ANSI_NULLS off as well. I don't hear of
any customers suing us... As for why we have it, well, this is a system
that started its life in 1992, when SQL Server had nothing else to
offer.
[color=blue][color=green][color=darkred]
>>> I don't think leaving the above mentioned options off amounts to a
>>> logical inconsistency i.e. 2+2=5.<<[/color][/color]
>
> I guess that you are smarter than ANSI, smarter than ISO, smarter than
> any other SQL product staff, etc.[/color]

Please take your insults somewhere else. Raziq has an application, and
provided the settings which are appropriate for the system, the
application performs his job, and he is happy with it.

One thing I can tell: he is smarter than you are, because he knows what
he can change with his system and you don't.
[color=blue]
> Do you exchange data with other SQL products? Do you move data from one
> table to another within the system itself? Do you plan on doing these
> things later? Do you want to have a Data Warehouse someday?[/color]

And how would any of that be affected by the fact that the application
internally uses NULL in the wrong way, or quotes it string literals with
" instead '?

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[--CELKO--]

Re: Ansi Nulls and quoted identified

>> And how would any of that be affected by the fact that the application
internally uses NULL in the wrong way, or quotes it string literals
with
" instead '? <<

IN() predicates will not work right when data is moved to the DW or to
a table with the right settings. Some of the other calculations can be
wrong.

Code will reference a column and not a literal when it is moved. My
favorite was "date" versus 'date' in one system I had to audit where
the poorly names column was a string.

Since 80% or mroe of the cost of an application is in maintaining it,
you want it to be immediately undestood by the next guy to take over
the job. Things as minor as inconsistent naming conventions can add
8-12% to the cost. What do you think that syntax that changes from
place within the application will cost?