Create Partition problem in VS2005 Team Edition for DB Professionals

Question

Create Partition problem in VS2005 Team Edition for DB Professionals

Rob-880668

Mr or Mrs. 500

Points: 573
More actions
April 17, 2009 at 11:03 am

#384388

I can't seem to find the problem. The project won't compile and shows the following error:
"TSD4001: The associated partition function 'my_partition_function' generates more partitions than there are file groups mentioned in the scheme 'my_partition'. (SQL error = 7707)"
I have the following definitions in my project (names removed).
CREATE PARTITION SCHEME [my_partition]
AS PARTITION [my_partition_function]
TO ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY])
CREATE PARTITION FUNCTION [my_partition_function]([varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS)
AS RANGE RIGHT
FOR VALUES (N'First', N'Second', N'Third', N'Fourth', N'Fifth')
Any help will be greatly appreciated.
Thanks,
Rob

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

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

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 1

Hi Rob

Seems you try to create two schemata within one SQL file. The concept of the DB-projects is to hold one object in one file. You have to create two different files with right click -> Add -> New Item -> Partition Schema (...Partition Function).

Greets

Flo

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Rob (4/17/2009)
I can't seem to find the problem. The project won't compile and shows the following error:
"TSD4001: The associated partition function 'my_partition_function' generates more partitions than there are file groups mentioned in the scheme 'my_partition'. (SQL error = 7707)"
I have the following definitions in my project (names removed).
CREATE PARTITION SCHEME [my_partition]
AS PARTITION [my_partition_function]
TO ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY])
CREATE PARTITION FUNCTION [my_partition_function]([varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS)
AS RANGE RIGHT
FOR VALUES (N'First', N'Second', N'Third', N'Fourth', N'Fifth')
Any help will be greatly appreciated.
Thanks,
Rob

You need 6 file groups for this partition scheme. Hard to go into the details, but if you want a working example my article on running totals has a good example of creating a partitioned table in it. Here is the link to my article for you to peruse: http://www.sqlservercentral.com/articles/T-SQL/65522/.

Rob-880668 Mr or Mrs. 500 Points: 573 More actions · Answer 3

Yes you are correct. A little while after I posted the question I happened to run into someone with the answer. He said that the function has a default that requires the scheme to have an extra partition. There's probably more to it than that but it got my work moving again.

Thanks.