Nov 12, 2012 at 4:05 PM
Edited Nov 12, 2012 at 6:50 PM
Can someone please help? I have googled and found that the constraint 'is not null' is required as well as the constraint on the column.
My partition function and scheme:
CREATE PARTITION FUNCTION [ItemsArchivePF1](int) AS RANGE right FOR VALUES (20111006)
CREATE PARTITION SCHEME [ItemsArchivePS1]
AS PARTITION [ItemsArchivePF1] ALL TO ([PRIMARY])
My partitioned table's DROP/DEFINE
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON
CREATE TABLE [dbo].[tblItemsArchive](
[ItemDateId] [int] NOT NULL,
[ItemSourceBank] [char](3) NOT NULL,
[ItemFileId] [int] NOT NULL,
[ItemId] [bigint] identity(1,1) NOT NULL,
[ItemDocType] [char](1) NOT NULL,
[ItemIsn] [char](12) NOT NULL,
[ItemPkt] [char](2) NOT NULL,
[ItemRejectReason] [char](2) NOT NULL,
[ItemKB_Count] [int] NOT NULL
CONSTRAINT itemPK PRIMARY KEY CLUSTERED (ItemDateId, ItemSourceBank, ItemId)
) ON [ItemsArchivePS1]([ItemDateId])
DATA_COMPRESSION = PAGE
SET ANSI_PADDING OFF
ObjectName IndexName IndexID PartitionScheme PartitionNumber FileGroupName LowerBoundaryValue UpperBoundaryValue Range Rows
tblItemsArchive itemPK 1 ItemsArchivePS1 1 PRIMARY NULL 20111006 RIGHT 0
tblItemsArchive itemPK 1 ItemsArchivePS1 2 PRIMARY 20111006 NULL RIGHT 0
When I run PartitionManager, the staging table created does not make the ItemId an identity field, so I manually changed it.
It is important for the ItemId to be an identity field in the daily staging table, but in the archive, it is okay not to be unique.
The constraint generated by PartitionManager reads:
ALTER TABLE [dbo].[staging_tblItemsArchive] WITH NOCHECK ADD CONSTRAINT [chk_myStagingTbl_partition_2] CHECK (([ItemDateId]>=(20111006)))
From googling other people who have had the same experience have added the equivalent of "[ItemDateId is not null and " to the constraint.
I have tried both ways and am going absolutley nuts.
What am I doing wrong?
My tables do not allow nulls in any of the fields.
Can my problem be with those 'ansi_padding' and 'ansi_nulls' statements?
Can my problem be with the identity column?
Should I have the identity field in yet another clone of the table, and copy that into the staging table and then into the archive partition? We will have one partition per day in the final table, never aging any data out. Only adding new data.
I use SSIS to load the staging table from a flat file, then attempt to switch it to partition 2.
ALTER TABLE staging_tblItemsArchive SWITCH TO tblItemsArchive PARTITION 2 ;
Msg 4972, Level 16, State 1, Line 1
ALTER TABLE SWITCH statement failed. Check constraints or partition function of source table 'PATS.dbo.staging_tblItemsArchive' allows values that are not allowed by check constraints or partition function on target table 'PATS.dbo.tblItemsArchive'.
I am pulling my hair out.