ANSI NULLS on Tables

awilbourn

I can't seem to find in the product where you can have if ANSI NULLS were enabled for a table. We recently ran into a problem where when the script was run it was not noted the mode and SQL Compare did not detect the differences. If this is a setting, what is it, otherwise please add that to a feature request.

Brian Donahue

Hi,

Having a brief look at this, ANSI NULL is a connection setting. If ANSI NULLS was set when the table was created, you would get ANSI-Style NULLS in the data, from what I understand. I cannot, however, figure out where SQL Server actually stores this information. I'd need to ask the developers if it is possible to detect the ANSI NULLS setting on an existing table. I suspect this is 'hidden' in SQL Server somewhere, but if it is not possible to retrieve this value, it may not be possible to support.

Thanks!

vbhackattack

-- See BOL for extact definitions
select type, name, objectproperty( object_id(name),'IsAnsiNullsOn') IsAnsiNullsOn, objectproperty( object_id(name),'ExecIsAnsiNullsOn') ExecIsAnsiNullsOn
from sysobjects
-- Exclude types for which these properties don't apply
where type not in ('C', 'D', 'F', 'K')
order by type, name

Brian Donahue

Thanks for your input. I've logged an issue with the development team asking them to see if they can support this. Also ANSI_PADDING, as a few requests have cropped up for this as well.