Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Partition a table

Author  Topic 

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2012-12-26 : 09:46:24
Hello,
I want to partition a table but am having problems with the partition creation

ALTER DATABASE Temp_DJJ ADD FILEGROUP MyTestFG;
GO
ALTER DATABASE Temp_DJJ ADD FILE (
name = Test_ReadOnly1,
filename = 'C:\SQLData\MyTestFG.ndf',
size = 10mb,
maxsize = 200mb,
filegrowth = 5mb) TO FILEGROUP MyTestFG;
GO
-- Try for two partitions on prior to 6/1/12 and one after that.
CREATE PARTITION FUNCTION TestPF (DATETIME) AS RANGE LEFT FOR VALUES ('6/1/2012');
GO
CREATE PARTITION SCHEME TestIt AS PARTITION TestPF TO ([PRIMARY], MyTestFG);
GO
On the CREATE PARTITION SCHEME I get the error:
Msg 208, Level 16, State 58, Line 1
Invalid object name 'MyTestFG'.


Not to mention I am unclear how to associate the table.

So where did I go wrong?
Thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-26 : 13:07:26
You have to create the partition function and scheme in the database - that may (or may not be the problem). Can you try this?:
ALTER DATABASE Temp_DJJ ADD FILEGROUP MyTestFG;
GO
ALTER DATABASE Temp_DJJ ADD FILE (
name = Test_ReadOnly1,
filename = 'C:\Temp\MyTestFG.ndf',
size = 10mb,
maxsize = 200mb,
filegrowth = 5mb) TO FILEGROUP MyTestFG;
GO
USE Temp_DJJ
GO

-- Try for two partitions on prior to 6/1/12 and one after that.
CREATE PARTITION FUNCTION TestPF (DATETIME) AS RANGE LEFT FOR VALUES ('6/1/2012');
GO
CREATE PARTITION SCHEME TestIt AS PARTITION TestPF TO ([PRIMARY], MyTestFG);
GO
Go to Top of Page

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2012-12-27 : 07:02:30
I knew it would be something simple. That worked.

Thank you!
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-27 : 07:27:08
Great!!

Now I can safely fess up! That was a wild guess based on the first sentence in this MSDN page which reads "Creates a function in the current database that maps..."
Go to Top of Page
   

- Advertisement -