January 28, 2008 at 5:19 am
Hi,
I have 300+ objects in my database that were created with 'ansi_nulls off'.
Is there a quick way of changing these so that they reflect a 'ansi_nulls on' setting?
Thanks,
Joe
January 28, 2008 at 7:42 am
I can't help you with your question, but ANSI_NULLS OFF affect only comparison operators. I wouldn't bother with legacy objects (that is, if you have choice). I had the opposite situation - I created bunch of procs with ANSI_NULLS ON from Sybase code and some of them did not work. I had to set ANSI_NULLS OFF for them.
Such change may introduce very tricky bugs, you should have good reason to change this setting 🙂
Piotr
...and your only reply is slàinte mhath
January 28, 2008 at 8:21 am
Piotr Rodak (1/28/2008)
I can't help you with your question, but ANSI_NULLS OFF affect only comparison operators. I wouldn't bother with legacy objects (that is, if you have choice). I had the opposite situation - I created bunch of procs with ANSI_NULLS ON from Sybase code and some of them did not work. I had to set ANSI_NULLS OFF for them.Such change may introduce very tricky bugs, you should have good reason to change this setting 🙂
Piotr
If you mean by "good reason", say - SET ANSI_NULLS OFF being a deprecated feature, well then - you have your wish....:). As I recall - it will continue to be supported in SQL 2008, but not after that.
To answer the initial question: I don't think there's a "quick way". You have to drop and recreate the object with the correct ANSI_NULLS.
Piotr is correct though - because it changes how operators work in relation to NULL values, it's usually not as simple as just "flipping the switch" and moving on. From my own experience, it was turned OFF for a reason, so that almost invariably means I have to change some of the underlying code....
Assuming it was only set to off by mistake, then you could use the generate scripts options to drop and recreate all of the objects in a single shot. Just test this thoroughly.
ugh - that sounds ugly.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 29, 2008 at 4:50 am
The quickest way (and as previously mentioned, you'll want to test this) is to script out all the objects in your database as Drop & Creates. Now you could use the Summary page of SSMS to choose only certain objects to script, but all the scripts will go to separate files / windows and you'll have to manually combine them.
I believe, though, that all the new CREATES will automatically have "Set Ansi_Nulls On", so once you have the scripts, all you have to do is run them.
[EDIT] OPPS! I'm wrong. I just tested choosing three SPs and they all combined into one window. Of course, I only got a choice of one command, CREATE or DROP, but still, a window for deletes and a window for creates is a lot better than having a single window for each individual object....
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply