Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2005
    Posts
    50

    Arrow Unanswered: Partitioning error

    Hi,

    I am trying to implement partitioning on a table
    depending upon the fiscal_month value...

    The current values are from 1-6...


    Create partition function LoadDataPartitionFunction ( smallint)
    as
    Range for values (1,2,3,4,5,6)


    -- drop partition scheme LoadDataPartitionScheme
    create partition scheme LoadDataPartitionScheme
    as
    Partition LoadDataPartitionFunction ALL to ([PRIMARY])


    CREATE TABLE Load_Data_Partition (
    [RowID] [int] NOT NULL,
    [Fiscal_Month] [smallint] NOT NULL,
    [Fiscal_Year] [smallint] NOT NULL,
    ....

    [Service] [nvarchar](100) COLLATE
    ) ON LoadDataPartitionScheme (Fiscal_Month)



    truncate table Load_Data_old --- same schema as load_data_partition
    Alter table load_data_partition switch partition 1 to Load_Data_old

    -- which month's data to be moved out
    alter partition function LoadDataPartitionFunction () merge range (1)


    Alter partition scheme LoadDataPartitionScheme next used [primary]

    -- which months data to be moved in
    alter partition function LoadDataPartitionFunction () split range(7)


    Select * from sys.partition_range_values


    function_id boundary_id parameter_id value
    ----------- ----------- ------------ -----
    65545 1 1 2
    65545 2 1 3
    65545 3 1 4
    65545 4 1 5
    65545 5 1 6
    65545 6 1 7





    Alter table [Load_Data_new] switch to [Load_Data_partition] partition 6



    ALTER TABLE SWITCH statement failed. Check constraints of source table Load_Data_new' allow values that are not allowed by range defined by partition 6 on target table 'Load_Data_partition'.


    Values in Load_Data_new for fiscal_month is 7


    But when i try


    Insert into [Load_Data_partition]
    Select * from [Load_Data_new]
    where fiscal_month = 7

    it works fine...

    reference used : http://www.sqlskills.com/resources/W...0Beta%20II.htm

  2. #2
    Join Date
    Jun 2005
    Posts
    50
    I got the answer..

    Alter table Load_Data_new add constraint load_data_new_month check ( fiscal_month =7)


    even though the Load_Data_new table has only month = 7 data...
    a constraint is mandatory.....

Posting Permissions

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