Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2006
    Posts
    4

    Unhappy Unanswered: Partitioned View

    Hi! This is my first post and I really need help with Partitioned View. I'm using Sql Server 2000 and I created a partitioned view using 6 tables and now a need to create the table '7' and alter the view. But when i'm trying to insert new data i'm receiving the message:
    "Server: Msg 4416, Level 16, State 5, Line 1
    UNION ALL view 'tb_sld_cob_pap' is not updatable because the definition
    contains a disallowed construct."

    My code is:

    drop VIEW tb_sld_cob_pap
    GO
    CREATE TABLE dbo.tb_sld_cob_pap_7 (
    cod_operacao int NOT NULL ,
    cod_contrato int NOT NULL ,
    sequencial_duplicata int NOT NULL ,
    data_sld_pap smalldatetime NOT NULL CHECK ([data_sld_pap] >= '20060201'),
    liqex_dia_nom_outros float NULL ,
    liqex_dia_moe_outros float NULL,
    constraint pk_pap7 primary key (cod_operacao,cod_contrato,sequencial_duplicata,da ta_sld_pap)
    )
    GO
    CREATE INDEX IdxSldCobPap7_1 ON dbo.tb_sld_cob_pap_7(cod_titulo, seq_titulo, data_sld_pap)
    GO

    CREATE INDEX IdxSldCobPap7_2 ON dbo.tb_sld_cob_pap_7(cod_operacao, seq_ctr_sacado, sequencial_duplicata, data_sld_pap)
    GO

    ALTER TABLE dbo.tb_sld_cob_pap_6
    DROP CONSTRAINT CK__tb_sld_co__data___6C190EBB
    GO

    ALTER TABLE dbo.tb_sld_cob_pap_6 ADD CONSTRAINT
    CK__tb_sld_co__data___6C190EBB CHECK (((([data_sld_pap] >= '20051201') and ([data_sld_pap] < '20060201'))))
    GO

    create VIEW tb_sld_cob_pap
    as
    select * from tb_sld_cob_pap_1
    union all
    select * from tb_sld_cob_pap_2
    union all
    select * from tb_sld_cob_pap_3
    union all
    select * from tb_sld_cob_pap_4
    union all
    select * from tb_sld_cob_pap_5
    union all
    select * from tb_sld_cob_pap_6
    union all
    select * from tb_sld_cob_pap_7

    My table tb_sld_cob_pap_6 does NOT have data with ([data_sld_pap] >= '20060201').
    I'm using this script in other database and I don't have this problem.



    Thank you...
    Last edited by nat_brasil; 03-06-06 at 14:30.

  2. #2
    Join Date
    May 2002
    Location
    General Deheza, Cba, Arg.
    Posts
    276
    test this (and tell me the result):

    create VIEW tb_sld_cob_pap
    WITH SCHEMABINDING
    as
    select cod_operacao,cod_contrato, sequencial_duplicata, data_sld_pap, liqex_dia_nom_outros, liqex_dia_moe_outros from tb_sld_cob_pap_1
    union all
    select cod_operacao,cod_contrato, sequencial_duplicata, data_sld_pap, liqex_dia_nom_outros, liqex_dia_moe_outros from tb_sld_cob_pap_2
    union all
    select cod_operacao,cod_contrato, sequencial_duplicata, data_sld_pap, liqex_dia_nom_outros, liqex_dia_moe_outros from tb_sld_cob_pap_3
    union all
    select cod_operacao,cod_contrato, sequencial_duplicata, data_sld_pap, liqex_dia_nom_outros, liqex_dia_moe_outros from tb_sld_cob_pap_4
    union all
    select cod_operacao,cod_contrato, sequencial_duplicata, data_sld_pap, liqex_dia_nom_outros, liqex_dia_moe_outros from tb_sld_cob_pap_5
    union all
    select cod_operacao,cod_contrato, sequencial_duplicata, data_sld_pap, liqex_dia_nom_outros, liqex_dia_moe_outros from tb_sld_cob_pap_6
    union all
    select cod_operacao,cod_contrato, sequencial_duplicata, data_sld_pap, liqex_dia_nom_outros, liqex_dia_moe_outros from tb_sld_cob_pap_7

  3. #3
    Join Date
    Feb 2006
    Posts
    4
    Tks achiola for the help but the result is the same...
    "Server: Msg 4416, Level 16, State 5, Line 1
    UNION ALL view 'tb_sld_cob_pap' is not updatable because the definition contains a disallowed construct."
    I try my script in a other database (for test) and it was Ok.

  4. #4
    Join Date
    May 2002
    Location
    General Deheza, Cba, Arg.
    Posts
    276
    ok, test create the table idem to another tables, example, you have a check by range, and another table >=;

    test creating the table:

    CREATE TABLE dbo.tb_sld_cob_pap_7 (
    cod_operacao int NOT NULL ,
    cod_contrato int NOT NULL ,
    sequencial_duplicata int NOT NULL ,
    data_sld_pap smalldatetime NOT NULL ,
    liqex_dia_nom_outros float NULL ,
    liqex_dia_moe_outros float NULL,
    constraint pk_pap7 primary key (cod_operacao,cod_contrato,sequencial_duplicata,da ta_sld_pap)
    )
    GO
    CREATE INDEX IdxSldCobPap7_1 ON dbo.tb_sld_cob_pap_7(cod_titulo, seq_titulo, data_sld_pap)
    GO

    CREATE INDEX IdxSldCobPap7_2 ON dbo.tb_sld_cob_pap_7(cod_operacao, seq_ctr_sacado, sequencial_duplicata, data_sld_pap)
    GO
    ALTER TABLE dbo.tb_sld_cob_pap_7 ADD CONSTRAINT
    CK__tb_sld_co__data___6C190EBB CHECK (((([data_sld_pap] >= '20060201') and ([data_sld_pap] <= '20101231'))))
    GO

  5. #5
    Join Date
    Feb 2006
    Posts
    4
    The error is the same...
    "Server: Msg 4416, Level 16, State 5, Line 1
    UNION ALL view 'tb_sld_cob_pap' is not updatable because the definition contains a disallowed construct."

  6. #6
    Join Date
    May 2002
    Location
    General Deheza, Cba, Arg.
    Posts
    276
    can you write the DDL of dbo.tb_sld_cob_pap_7 and dbo.tb_sld_cob_pap_6?

    thanks
    Abel.

  7. #7
    Join Date
    Feb 2006
    Posts
    4
    The table '6' have this original DDL:

    CREATE TABLE dbo.tb_sld_cob_pap_6 (
    cod_operacao int NOT NULL ,
    cod_contrato int NOT NULL ,
    sequencial_duplicata int NOT NULL ,
    data_sld_pap smalldatetime NOT NULL CHECK (([data_sld_pap]>='20051201')),
    liqex_dia_nom_outros float NULL ,
    liqex_dia_moe_outros float NULL,
    constraint pk_pap6 primary key (cod_operacao,cod_contrato,sequencial_duplicata,da ta_sld_pap)
    )
    CREATE INDEX IdxSldCobPap6_1 ON dbo.tb_sld_cob_pap_7(data_sld_pap)
    GO
    CREATE INDEX IdxSldCobPap6_2 ON dbo.tb_sld_cob_pap_7(cod_operacao, sequencial_duplicata, data_sld_pap)
    GO

    Now that I'm trying to change my view to code is:

    ALTER TABLE dbo.tb_sld_cob_pap_6
    DROP CONSTRAINT CK__tb_sld_co__data___6C190EBB
    GO
    ALTER TABLE dbo.tb_sld_cob_pap_6 ADD CONSTRAINT
    CK__tb_sld_co__data___6C190EBB CHECK (((([data_sld_pap] >= '20051201') and ([data_sld_pap] < '20060201'))))

    GO
    CREATE TABLE dbo.tb_sld_cob_pap_7 (
    cod_operacao int NOT NULL ,
    cod_contrato int NOT NULL ,
    sequencial_duplicata int NOT NULL ,
    data_sld_pap smalldatetime NOT NULL CHECK ([data_sld_pap] >= '20060201'),
    liqex_dia_nom_outros float NULL ,
    liqex_dia_moe_outros float NULL,
    constraint pk_pap7 primary key (cod_operacao,cod_contrato,sequencial_duplicata,da ta_sld_pap)
    )
    CREATE INDEX IdxSldCobPap7_1 ON dbo.tb_sld_cob_pap_7(data_sld_pap)
    GO
    CREATE INDEX IdxSldCobPap7_2 ON dbo.tb_sld_cob_pap_7(cod_operacao, sequencial_duplicata, data_sld_pap)
    Last edited by nat_brasil; 03-07-06 at 13:29.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •