0

So while I understand what it's for and why I want it on. I have a customer database that has some how been created with it off. There seems to be lots of places to change this value, on the server, on the db in SSMS options

My confusion is how do I know if objects have been created with it on or off.

When I right click on an SP or FN and click Modify it is always adding in SET ANSI_NULLS OFF when it should be SET ANSI_NULLS_ON which leads me to believe it's been stored as OFF and not ON

What I've not been able to make happen is if I drop and create an object with SET ANSI_NULLS ON when I use the modify option it still says OFF.

I'm sure I'm just not understanding something.

I did find 

SELECT OBJ.type_desc AS ObjectType,  OBJ.name AS ObjectName
FROM sys.objects AS OBJ
INNER JOIN sys.sql_modules AS CODE
    ON CODE.object_id = OBJ.object_id
WHERE CODE.uses_ansi_nulls = 0
ORDER BY ObjectType, ObjectName

Which is telling me which objects have ANSI_NULLS off but everything I do to turn the on seems to not work.

Chris Ward
  • 771
  • 1
  • 9
  • 23
  • An even weirder thing is I dropped an SP that showed up in the query and now the SP is gone but the above query says it's still there. – Chris Ward Nov 21 '19 at 21:38
  • You'll have to drop and recreate the affected objects preceded by `set ansi_nulls on`. This should be easy, for some definition of easy, if all the affected objects are in source control. – AlwaysLearning Nov 21 '19 at 21:43
  • I've tried that when I drop and recreate they still show as off – Chris Ward Nov 22 '19 at 18:10

0 Answers0