February 19, 2009 at 7:25 am
Hi,
I need to change ANSI_NULLS for a table that was created with ANSI_NULLs off.
when i wrote
use database
SET ANSI_NULLs ON
It doesn't change this option for the table.
thanks in advance
February 19, 2009 at 7:43 am
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
February 19, 2009 at 7:47 am
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 .
February 19, 2009 at 7:49 am
Then change it on the database level in properties under options change it from false to true.
Kind regards,
Gift Peddie
February 19, 2009 at 7:52 am
I 've changed the option for the database
Unfortunately, it doesn't change ANSII_NULLs option for the table
February 19, 2009 at 8:04 am
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
February 19, 2009 at 8:37 am
Restart of the server didn't help.
The Sql-
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?
February 19, 2009 at 9:05 am
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]
GO
/****** Object: Table [Sales].[Store] Script Date: 02/19/2009 11:02:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
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
February 19, 2009 at 9:53 am
My question is "How to change the option ANSI_NULLs of existing table?"
February 19, 2009 at 9:58 am
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
February 19, 2009 at 10:08 am
Unfortunately, it doesn't help
February 19, 2009 at 10:18 am
I am not aware of any issues with it so the only other option is to change the server with sp_configure.
http://msdn.microsoft.com/en-us/library/ms190356.aspx
Kind regards,
Gift Peddie
February 19, 2009 at 10:19 am
Would it be possible to get a detailed explanation of what your are trying to accomplish here?
December 17, 2021 at 5:12 am
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.
----------------------------------------------------
December 17, 2021 at 2:40 pm
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