Change ANSI_NULLs option for a table

  • Hi,

    I need to change ANSI_NULLS for a table that was created with ANSI_NULLs off.

    when i wrote

    use database


    It doesn't change this option for the table.

    thanks in advance

  • You could change it in the database properties under options, if you want to do it on the table level you may need code to do it.

    Kind regards,
    Gift Peddie

  • what code do you mean?

    I can't recreate the table because ther are a lot of data in it.

    It lookup table, so many other tables are connected to it .

  • Then change it on the database level in properties under options change it from false to true.

    Kind regards,
    Gift Peddie

  • I 've changed the option for the database

    Unfortunately, it doesn't change ANSII_NULLs option for the table

  • If it is possible try restarting the box if not you have to use it in your code until you can ALTER the table to SET ANSI NULL on and per Microsoft ANSI NULL ON will be required in future versions of SQL Server.

    Kind regards,
    Gift Peddie

  • Restart of the server didn't help.

    The Sql-

    alter table tablename


    is not correct

    I get the error "Incorrect syntax near the keyword 'set'."

    Do you know the correct syntax?

  • This is the script of the Sales table from AdventureWorks. When you script your table you will see the SET ANSI NULL OFF.

    USE [AdventureWorks2008]


    /****** Object: Table [Sales].[Store] Script Date: 02/19/2009 11:02:15 ******/





    CREATE TABLE [Sales].[Store](

    [BusinessEntityID] [int] NOT NULL,

    [Name] [dbo].[Name] NOT NULL,

    [SalesPersonID] [int] NULL,

    [Demographics] [xml](CONTENT [Sales].[StoreSurveySchemaCollection]) NULL,

    [rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL,

    [ModifiedDate] [datetime] NOT NULL,

    Kind regards,
    Gift Peddie

  • My question is "How to change the option ANSI_NULLs of existing table?"

  • That is what I covered with my previous post right click on your table and go to script out in a query window and you will see the SET ANSI NULL OFF. You need to change it to ON just execute it as an ALTER statement.

    Please do this on a backup copy of your database in a test box.

    Kind regards,
    Gift Peddie

  • Unfortunately, it doesn't help

  • I am not aware of any issues with it so the only other option is to change the server with sp_configure.

    Kind regards,
    Gift Peddie

  • Would it be possible to get a detailed explanation of what your are trying to accomplish here?

  • Old post, but I ran into this issue recently.

    I get an error when trying to create a 'filtered' non clustered index only. A non filtered variety works just fine. Seems the best way is to recreate the table as a copy with ansi_nulls on , then switch the two tables

    ALTER TABLE OriginalTable  SWITCH TO NewTable;

    combined with a (drop or rename of the orignal table, rename the new table to the name of the orignal table via sp_rename).

    The problem here is I am not sure what existing code/Stored procedures could be affected that source from the original table. Maybe the best thing is to leave out the filter part of the index to avoid this whole situation.



  • Have you tried setting the correct settings and then create the filtered index, or is it failing because the underlying table has the wrong settings?


Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply