Post reply

Indexed View

  • December 8, 2003 at 1:15 am

    #81686

    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

    #485055

    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

    #485058

    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

    #485240

    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

    #485337

    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

    #485479

    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