SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Partition a table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

djj55
Constraint Violating Yak Guru

USA
311 Posts

Posted - 12/26/2012 :  09:46:24  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/26/2012 :  13:07:26  Show Profile  Reply with Quote
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

USA
311 Posts

Posted - 12/27/2012 :  07:02:30  Show Profile  Reply with Quote
I knew it would be something simple. That worked.

Thank you!

Edited by - djj55 on 12/27/2012 07:03:12
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/27/2012 :  07:27:08  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000