locked
Partition switching fails because of range values RRS feed

  • Question

  • Hello Everybody,

    I need to reload every day a partition  using SSIS.
    In order to test the switching operation prior to integrating it with SSIS i've tested it first in
    SSMS. First i switched the existing values in the partition into a temp table without any check constraints. This worked perfectly and when querying the partition no data is returned.
    However when adding a constraint on the temp table, limiting the year column to one specific year (partitioned table is based on year) and trying to switch the data back into the partition I get the following error:

    ALTER TABLE SWITCH statement failed. Check constraints or partition function of source table 'MIS_DWH.MIS.temp_output_unpivot_sales_fact' allows values that are not allowed by check constraints or partition function on target table 'MIS_DWH.mis.unpivot_sales_fact'.

    Partitioned table is partitioned based on year. Has no clustered index and no primary key (Production environment does not provide a primary key for the order details)This lack of primary key is the reason why i need to recalculate the partitions every day...
    Temp table has one constraint added after initial switch. no clustered index and primary key...
    Any body any idea what could be the cause of this behaviour and what do i need to do to solve this issue??
    Thanks in advance

    Friday, July 20, 2007 11:16 AM

Answers

  • Nobody any idea??!?

    Seems I'm always encountering unresovable problems (with SQL server, Reporting Services...).... :-(

    Anyway, I could found a work around however, it would be nice to know why this doesn't work.

    Thanks in advance



    For the ones who are interested: The principle of sliding partition windows is having a number of partitions defined allowing to keep the data for a certain amount of time... Once , the left side of the partition becomes out of scope of the partitioning schema it will be definitely removed from the partition and the right side will include a new range...

    However As I needed to keep all the data, the only thing i needed to do in order to update quickly my partitions without isuing update/delete statements was to have an additional table called for example 'remove_partition_data' and switch the partition that needed to be updated to this table, Load the data again in the partition, drop the remove table and recreate it in the next filegroup containing the partition that had to be updated...

    This way, data is quickly loaded and doesn't overload the server with index management due to update/delete or insert statements...




    Wednesday, July 25, 2007 7:56 AM

All replies

  • Please post your partition scheme, function, table definition, and the insert statement you are using to insert data into the table. 
    Friday, July 20, 2007 1:27 PM
  • Hello,

     

    thanks for your reply Tim.

     

    As requested the DDL statements for partition function,scheme and switch statement:


    CREATE PARTITION FUNCTION [unpivot_em](smallint) AS RANGE LEFT FOR VALUES (1989, 1990, 1991, 1992, 1993, 1994, 1995, 1996, 1997, 1998, 1999, 2000, 2001, 2002, 2003, 2004, 2005, 2006, 2007, 2008, 2009, 2010, 2011, 2012, 2013, 2014, 2015, 2016, 2017, 2018, 2019, 2020)


    CREATE PARTITION SCHEME [unpivot_em_scheme] AS PARTITION [unpivot_em] TO ([FG1989], [FG1990], [FG1991], [FG1992], [FG1993], [FG1994], [FG1995], [FG1996], [FG1997], [FG1998], [FG1999], [FG2000], [FG2001], [FG2002], [FG2003], [FG2004], [FG2005], [FG2006], [FG2007], [FG2008], [FG2009], [FG2010], [FG2011], [FG2012], [FG2013], [FG2014], [FG2015], [FG2016], [FG2017], [FG2018], [FG2019], [FG2020], [FG_INFINITE])

     

     

    Initial load of the table is done using SSIS and an OLEDB destination component.

     

    Switch statement to remove data from the partition:

     

    alter table mis.unpivot_Sales_fact

    switch partition 18 to mis.temp_output_unpivot_sales_fact

     

    adding constraint:

     

    alter table mis.temp_output_unpivot_sales_fact

    with check

    add constraint SalesFactMaxRange2006

    check(year = 2006)

     

    switching back into partition:

     

    alter table mis.temp_output_unpivot_sales_fact

    switch to mis.unpivot_sales_fact partition 18

     

    Hope you have enough information to help me debugging this problem

    Anyway thanks in advance for your support

     

     

    Friday, July 20, 2007 1:39 PM
  • Nobody any idea??!?

    Seems I'm always encountering unresovable problems (with SQL server, Reporting Services...).... :-(

    Anyway, I could found a work around however, it would be nice to know why this doesn't work.

    Thanks in advance



    For the ones who are interested: The principle of sliding partition windows is having a number of partitions defined allowing to keep the data for a certain amount of time... Once , the left side of the partition becomes out of scope of the partitioning schema it will be definitely removed from the partition and the right side will include a new range...

    However As I needed to keep all the data, the only thing i needed to do in order to update quickly my partitions without isuing update/delete statements was to have an additional table called for example 'remove_partition_data' and switch the partition that needed to be updated to this table, Load the data again in the partition, drop the remove table and recreate it in the next filegroup containing the partition that had to be updated...

    This way, data is quickly loaded and doesn't overload the server with index management due to update/delete or insert statements...




    Wednesday, July 25, 2007 7:56 AM