ALTER TABLE SWITCH statement failed..

Jan 26, 2012 at 11:46 AM

I got this error when trying to use /c:ClearPartition

Msg 4972, Level 16, State 1, Line 1

ALTER TABLE SWITCH statement failed. Check constraints or partition function of source table 'Sony_PartitionTest.dbo.Article' allows values that are not allowed by check constraints or partition function on target table 'Sony_PartitionTest.dbo.myStagingTbl3'.

the error seem to be related to how the date format is interpreted by the Partitinon Manager tool.

([ProcessedDate]>=N'07/06/2011 00:00:00' AND [ProcessedDate]<N'01/07/2011 00:00:00'

) 

 

If I set the date format in sql management studio, then perform the Switch manualy, it seems to work

SET

 DATEFORMAT dmy

;

ALTER

TABLE dbo.Article SWITCH PARTITION 2 TO dbo.ArticleStage

But it does not work using partition manager .

Is there away I can change the way partition manager sees the date? or run set datetformat dmy before the swittch is called ?

thanks alot

Yasir

Jan 27, 2012 at 3:59 AM

What happens if you specify the desired dates in MYD format when you invoke the command?

-- Stuart

Jan 30, 2012 at 4:12 PM

thanks for your reply, sorry I should have checked that you can pass in the data as a parameter

/PartitionRangeValue:<string> /v Value string to input to partition function to specify partition number

great tool by the way.

 

Coordinator
Jan 30, 2012 at 6:41 PM

How were you passing the partition ID information before that led to a failure?

From: Yasirbam [email removed]
Sent: Monday, January 30, 2012 9:13 AM
To: Stuart Ozer
Subject: Re: ALTER TABLE SWITCH statement failed.. [SQLPartitionMgmt:287481]

From: Yasirbam

thanks for your reply, sorry I should have checked that you can pass in the data as a parameter

/PartitionRangeValue:<string> /v Value string to input to partition function to specify partition number

great tool by the way.

Jan 31, 2012 at 3:35 PM

I was testing using one of the examples in the readme file using a hardcoded partitionId=2 (which is the partition I am interested)

ManagePartition/C:CreateStagingFull /d:sample /s:dbo /t:Address /p:2 /A:myStagingTbl

 

Coordinator
Nov 29, 2012 at 3:48 AM

Fixed in version 3.0

Jun 25, 2013 at 4:35 PM
Hi All,

I had the same error as above. It took me a few days to figure out the problem.


Msg 4972, Level 16, State 1, Line 1

ALTER TABLE SWITCH statement failed. Check constraints or partition function of source table XXXX allows values that are not allowed by check constraints or partition function on target table YYY.

The DDL for the Aux table (Check contraints, indexes, etc) was fine prior to loading the data with the SSIS data flow task. We could switch in previously loaded data via sql at that time. However the destination component in the data flow was changing the DDL of the check constraint since I did not have the check box checked for "Check contraints". Checking this box kept the DDL as it was after the command below was run (to switch out the partition). This also allowed me to run the switch statement correctly.

ManagePartition /C:ClearPartition /d:ABC /s:dbo /A:xxxFactAux /t:xxxFact /v:1 /K:+

Hopefully this saves ytou some time!

Thanks,
SW