locked
help switching from non partition table to partitioned table RRS feed

  • Question

  • Good morning,

    I've got a partition function which looks like this:

    CREATE PARTITION FUNCTION [fnSmartSearchResults](smalldatetime)
     AS RANGE RIGHT FOR VALUES (
    N'2009-02-01T00:00:00'
    , N'2009-03-01T00:00:00'
    , N'2009-03-02T00:00:00'
    , N'2009-03-03T00:00:00'
    , N'2009-03-04T00:00:00'
    , N'2009-03-05T00:00:00')

    Then I got a partition scheme which looks like this:

    CREATE PARTITION SCHEME [SmartSearchPSchemeResults] AS PARTITION [fnSmartSearchResults] TO(
    [PRIMARY]
    , [SS_Results_20090301]
    , [SS_Results_20090302]
    , [SS_Results_20090303]
    , [SS_Results_20090304]
    , [SS_Results_20090305]
    ,[PRIMARY]
    )

    I got a table created on this partition scheme as follow:

    CREATE TABLE [dbo].[ssv5_results_load](
    [smart_search_result_id] [bigint] NOT NULL,
    [smart_search_id] [bigint] NOT NULL,
    [provider_id] [tinyint] NOT NULL,
    [smart_result_type_id] [bit] NOT NULL,
    [smart_result_dmc_id] [smallint] NOT NULL,
    [smart_result_dmc_code] [varchar](10) NOT NULL,
    [smart_result_hotel_id] [int] NOT NULL,
    [SearchDate] [smalldatetime] NOT NULL,
    [smart_result_included] [varchar](1000) NULL,
    [smart_result_excluded] [varchar](1000) NULL,
    [smart_result_promotion] [nvarchar](1000) NULL,
    [smart_result_completed] [tinyint] NULL,
    smart_result_payment_model] [tinyint] NULL
    CONSTRAINT [PK_ssv5_results1200_load2] PRIMARY KEY CLUSTERED
    ([smart_search_result_id] ASC,
    [SearchDate] ASC,
    [smart_search_id] ASC) ON [SmartSearchPSchemeResults] (Searchdate)
    ) ON [SmartSearchPSchemeResults] (Searchdate)

    And I got a staging table which is basically the same for just one month:

    CREATE TABLE [dbo].[ssv5_results_load](
    [smart_search_result_id] [bigint] NOT NULL,
    [smart_search_id] [bigint] NOT NULL,
    [provider_id] [tinyint] NOT NULL,
    [smart_result_type_id] [bit] NOT NULL,
    [smart_result_dmc_id] [smallint] NOT NULL,
    [smart_result_dmc_code] [varchar](10) NOT NULL,
    [smart_result_hotel_id] [int] NOT NULL,
    [SearchDate] [smalldatetime] NOT NULL,
    [smart_result_included] [varchar](1000) NULL,
    [smart_result_excluded] [varchar](1000) NULL,
    [smart_result_promotion] [nvarchar](1000) NULL,
    [smart_result_completed] [tinyint] NULL,
    smart_result_payment_model] [tinyint] NULL
    CONSTRAINT [PK_ssv5_results1200_load2] PRIMARY KEY CLUSTERED
    ([smart_search_result_id] ASC,
    [SearchDate] ASC,
    [smart_search_id] ASC) ON [SS_Results_20090301]
    ) ON [SS_Results_20090301]

    ALTER TABLE ssv5_results_load add constraint check_date
    CHECK (searchdate >= '2009/03/01' AND searchdate < '2009/03/02')
    searchdate >= '2009/03/01 and searchdate < '2009/03/02'

    Finally, I load the data in there (just a few for test purpose):

    INSERT INTO Agoda_Smartsearch_v5_new.dbo.ssv5_results_load2
    SELECT TOP 1000 *
    FROM Agoda_SmartSearch_V5.dbo.ssv5_results
    WHERE SEARCHDATE >= '2009/03/01'
    AND SEARCHDATE < '2009/03/02'

    ALTER TABLE ssv5_results_load SWITCH TO Agoda_Smartsearch_v5_new.dbo.ssv5_results PARTITION 2

    And this is when the problem occurs:

    Msg 4972, Level 16, State 1, Line 1
    ALTER TABLE SWITCH statement failed. Check constraints or partition function of source table 'Agoda_Smartsearch_v5_new.dbo.ssv5_results_load' allows values that are not allowed by check constraints or partition function on target table 'Agoda_Smartsearch_v5_new.dbo.ssv5_results'.

    Additional information:
    select * from sys.partition_range_values

    fn id    boundary id    parameter id    value
    65540     1                 1                 2009-02-01 00:00:00.000
    65540     2                 1                 2009-03-01 00:00:00.000
    65540     3                 1                 2009-03-02 00:00:00.000
    65540     4                 1                 2009-03-03 00:00:00.000
    65540     5                 1                 2009-03-04 00:00:00.000
    65540     6                 1                 2009-03-05 00:00:00.000

    Can somone help me sort this out. 

    I feel like I'm pretty close to the problem. I don't understand how my data wouldn't match the values allowed by the partition considering all the effort to make sure that only the right data gets in the staging table.

    Thanks in advance,

    Nico

    Thursday, March 5, 2009 4:50 AM

Answers

  • HI.

    I've found the problem.

    Was loading to the wrong partition. I got one too much partition pushing everything 1 to the right.
    I've changed my function definition and scheme. It now works.
    Thursday, March 5, 2009 6:13 AM

All replies

  • HI.

    I've found the problem.

    Was loading to the wrong partition. I got one too much partition pushing everything 1 to the right.
    I've changed my function definition and scheme. It now works.
    Thursday, March 5, 2009 6:13 AM
  • Hi Nicoin 

    I'm dealing with same issue and your post is the only post i have found online that directly addresses the same issue i'm having. I know this is a very old thread but i'm hoping you can share the changes you made to your function definition and scheme.

    Thanks in advanced,

    Nino

    Tuesday, September 24, 2019 9:00 PM