Indexed View

  • 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.

  • 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.

  • 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.

  • 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..

  • 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
  • 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