December 8, 2003 at 1:15 am
I am unable to Create Index on a View Since one of the Tables was Created using ANSI_NULL off.
Is there any way i can change table ANSI_NULL settings to ON without recreating the table and importing data back.
December 8, 2003 at 5:11 am
Are you sure that it is one of the tables causing the problem? I've ran into this many times, but never with a table.
December 8, 2003 at 5:55 am
I am pretty much sure since the error message clearly indicates the table name. The whole database was migrated from 6.5 and the tables i am joining is having huge data and is growing every day.
December 8, 2003 at 11:07 pm
I presume the only way is to Drop and Recreate the table with ANSI_NULLS on and import the data back. There goes my Weekend.
Thanks anyway..
December 9, 2003 at 8:11 am
While I reproduced your issue below to make sure I understood, the only way I know of to fix it would be to reload as you said.
This shouldn't take a weekend I wouldn't think. All you have to do is create an identical structure and copy the data into it- just make sure that any null comparisons within the table/constraint/field definitions use ansi_null on compatable syntax.
If you use sp_rename be careful. Not sure in sql2k but in 7.0 I discovered that if I didn't actually drop the original table when I was done then all compiled sps referencing it would continue to (by object id which doesn't change).
drop view vJunk
go
drop table junk1
go
drop table junk2
go
set ANSI_NULLS on
set ANSI_PADDING on
set ANSI_WARNINGS on
set ARITHABORT on
set CONCAT_NULL_YIELDS_NULL on
set QUOTED_IDENTIFIER on
set NUMERIC_ROUNDABORT OFF
go
create table junk1(myfield int not null)
go
set ansi_nulls off
create table junk2(myfield int not null)
go
create view dbo.vJunk with schemabinding as
select
a.myfield as MyField1
,b.myfield as MyField2
from
dbo.junk1 as a
inner join dbo.junk2 as b on a.myfield = b.myfield
go
create clustered index cixvJunk_MyField1 on dbo.vJunk(MyField1)
go
select * from dbo.vJunk
December 10, 2003 at 2:20 am
Thanks for the reply. I exactly planned to do the same. but i need to put the database offline to do this excercise and data we talking about 5 years of data. Not to mention the Views,Triggers & other Dependent Object on the table to be recreated.
Thanks for you time.
Edited by - edwin_fredrick on 12/10/2003 04:37:40 AM
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply