SQL 2008 Scheme instead of FileGroup

Apr 23, 2010 at 11:05 PM

I wanted to create a very simple Partitioning example where all Partitions are on the same FileGroup (to avoid the mess of having a bunch of FileGroups). I used the "ALL" keyword when creating my Scheme.

Given:
CREATE PARTITION SCHEME PS_Foo
   AS PARTITION PFR_Foo_DateTime
  ALL TO ( FG_TestPartitionedLogs ) ; 

CREATE TABLE [dbo].[logging_events]
... ON  PS_Foo(logging_event_timestamp);

Running ManagePartition command results in error
"Create failed for Table 'dbo.logging_events_part2_634076331421614463'.
An exception occurred while executing a Transact-SQL statement or batch. Invalid filegroup 'PS_Foo' specified"
Note that 'PS_Foo' is the Scheme name, not the FileGroup. I'm guessing another case needs to be accounted for that will map the Scheme to the FileGroup when the Scheme uses "ALL".

 

Coordinator
Nov 21, 2012 at 11:07 PM

I expect the problem is a bug in my tool that affects operation when your table contains a long text or long binary column.   I tested you example on a simple table and received no errors.  Can you send me or post the DDL for the original table and I will verify that my upcoming fix will address this situation?

 

Nov 22, 2012 at 8:50 AM

Hi Stuarto

I am attaching my archive table description for you.

I think my confusion came from

a) Running your utility to create the staging table,

b) Noticing that the ItemId in the staging table was not set to identity

c) Using SSMS to script the staging table as ‘drop & create’

a. this is where I found the constraints to be confusing.

The way I got the staging table to work for me was to

a) define it with no index or constraints

b) load the data

c) add index and constraints the constraints being in the attached stored procedure

d) switch it into the partition

e) delete index and constraints from staging table in preparation for next load

f) rebuild statistics on archive table

Eileen Eby

Svc Info Developer IV, Technical Design Team

Hewlett-Packard Company

+44 1327 264 057 / Tel

+44 7768 700 755 / Mobile

eileen.eby@hp.com / Email

44 Farndon Rd

Woodford Halse, Daventry, NN11 3TT

UK

Please consider the environment before printing this email.

From: stuarto [email removed]
Sent: 21 November 2012 23:08
To: Eby, Eileen
Subject: Re: SQL 2008 Scheme instead of FileGroup [SQLPartitionMgmt:210371]

From: stuarto

I expect the problem is a bug in my tool that affects operation when your table contains a long text or long binary column. I tested you example on a simple table and received no errors. Can you send me or post the DDL for the original table and I will verify that my upcoming fix will address this situation?

Coordinator
Nov 29, 2012 at 4:47 AM

Fixed in version 3.0.