Post reply

Problems with partition

  • May 1, 2006 at 7:46 am

    #366087

    Hi folks,

    I am trying to create a partitioned table with SQL Server 2005.  I have defined the scheme and partition function ok, however, when I try to create the table, it gives me the following error: 

    Partition function 'MonthlyDateRangePFN' uses 1 columns which does not match with the number of partition columns used to partition the table or index.

    Here are my scripts:

    Partition Function

    CREATE

    PARTITION FUNCTION [MonthlyDateRangePFN](datetime) AS RANGE LEFT FOR VALUES

    (

    N'2005-01-31 23:59:59', N'2005-02-28 23:59:59', N'2005-03-31 23:59:59',

    N'2005-04-30 23:59:59', N'2005-05-31 23:59:59', N'2005-06-30 23:59:59',

    N'2005-07-31 23:59:59', N'2005-08-31 23:59:59', N'2005-09-30 23:59:59',

    N'2005-10-31 23:59:59', N'2005-11-30 23:59:59', N'2005-12-31 23:59:59',

    N'2006-01-31 23:59:59', N'2006-02-28 23:59:59', N'2006-03-31 23:59:59',

    N'2006-04-30 23:59:59', N'2006-05-31 23:59:59', N'2006-06-30 23:59:59',

    N'2006-07-31 23:59:59', N'2006-08-31 23:59:59', N'2006-09-30 23:59:59',

    N'2006-10-31 23:59:59', N'2006-11-30 23:59:59', N'2006-12-31 23:59:59')

    Partition Scheme

    USE

    [MARS_ENT]

    GO

    /****** Object: PartitionScheme [MonthlyDateRangePScheme] Script Date: 04/30/2006 22:54:56 ******/

    CREATE

    PARTITION SCHEME [MonthlyDateRangePScheme] AS PARTITION [MonthlyDateRangePFN] TO (

    [200501], [200502], [200503], [200504], [200505], [200506],

    [200507], [200508], [200509], [200510], [200511], [200512],

    [200601], [200602], [200603], [200604], [200605], [200606],

    [200607], [200608], [200609], [200610], [200611], [200612],

    [PRIMARY])

     

    Create table script

    Create table PartitionTest

    (

    EffectiveDate datetime

    Constraint PartitionTestCK

    CHECK ([EffectiveDate] >= '20050101'

    AND [effectivedate] <= '20061231')) on [MonthlyDateRangePScheme]

     

    I have followed the books online article step by step and cannot find anything that I may have missed.  I can change the table filegroup to PRIMARY and it works fine but that's not what I want.  I need to send data from different dates to different drive.  Any help will be appreciated.

    Thanks

    Eric

  • May 1, 2006 at 7:01 pm

    #635239

    alright I figured it out.  I was missing the field name parameter in filegroup defination in create table script.

    Thanks.

  • May 2, 2006 at 2:53 pm

    #635463

    Hi Eric,

    Quest Capacity Manager provides Index and Table Partition management. It can generate scripts for:

    Creating Partition Table, partition function, partition scheme, merge partitions, split partitions, view dependencies and Aligned objects, etc.  

    You can download the product from following link.

    http://www.quest.com/capacity_manager_for_sql_server/

     

    Regards,

    Hassan Fahimi

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply