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]

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]

To identify the objects, use something like this:

SELECT name, xtype,
OBJECTPROPERTY(id,'ExecIsAnsiNullsOn'),
OBJECTPROPERTY(id,'ExecIsQuotedIdentOn')
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]

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]

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

Razvan

[Razvan Socol]

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

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

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

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

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]

Razvan Socol (rs****@gmail.com) writes:

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

Hm, if you have a procedure that reads:

CREATE PROCEDURE ill_will AS
PRINT "Hej!"

and it was loaded with QUOTED_IDENTIFIER off, and you change it to ON,
I bet you see have side effects!
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Erland Sommarskog]

Raziq Shekha (ra**********@anadarko.com) writes:

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

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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[--CELKO--]

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

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

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]

--CELKO-- (jc*******@earthlink.net) writes:

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

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

I guess that you are smarter than ANSI, smarter than ISO, smarter than
any other SQL product staff, etc.

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

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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[--CELKO--]

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

[Raziq Shekha]

Thank you Erland Sommarskog. I guess Celko does not realize that there
is life outside a database and IT. Celko, you might want to look up the
following words:

1) Dating
2) dancing
3) relaxing
4) taking it easy
5) anal

And no, don't look these words up on IT.com.

Asif.


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

[AK]

>> Why do you wish to destroy portability and data integrity? The only
reasons I can imagine are (1) ignorance of SQL (2) deliberate
sabotage. <<

an excerpt from Real Life Databases 100 (as opposed to theoretical
databases in a perfect world):
Most software vendors discourage portability, and the reason is
obvious: they want to retain their customers, so they don't want to
make porting out of their products easy.

[Erland Sommarskog]

--CELKO-- (jc*******@earthlink.net) writes:

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.
Presumably the DW is another database. And very likely in Analysis
Services. What the ANSI_NULLS setting is in the source system is
irrelevant.
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.
We already knows the answer to that one: vendor mandates which setting
that should be used.

By the same reasoning, you should never denote your tables as "Order
Details" in the code, but use [Order Details], because the code may be
loaded with QUOTED_IDENTIFIER off. (And, alas, this is the default with OSQL
and Enterprise Manager.)
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?

That's Raziq's vendor's problem. Assume that they started to write
their application in 1992, like we did. Then " to quote strings was
a fair game in SQL Server. Guess what it would cost them to fix that?
Compare that with mandating the application to be installed with
QUOTED_IDENTIFIER off.

As it happens, our source code also uses both " and ' to delimit strings.
However, I've augmented our load tool to take replace all " with '. But
that is not really a trivial operation, and I have full understanding
for those who prefer to leave it as it is.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp