I need to change ANSI_NULLS for a table that was created with ANSI_NULLs off.
when i wrote
SET ANSI_NULLs ON
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.
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.
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.
Restart of the server didn't help.
alter table tablename
set SET ANSI_NULLs ON
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.
/****** Object: Table [Sales].[Store] Script Date: 02/19/2009 11:02:15 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
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,
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.
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.
Would it be possible to get a detailed explanation of what your are trying to accomplish here?
One Orange Chip
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)