Partition Table Switch Error

Question

Partition Table Switch Error

CrazyMan

SSChampion

Points: 12560
More actions
November 22, 2010 at 12:55 pm

#229030

Hi All
I have created a partition table on a database and I can see that the partition has been created. I have created an other table where i want to switch the partition to, This partition table has got all the same structure, but when i tried to switch partition i am getting the below error.
SQL used to switch the partition
ALTER TABLE CDR_0_test
SWITCH PARTITION 10
TO [CDR_0_Test_Part]
GO
Error recived
Msg 4913, Level 16, State 1, Line 1
ALTER TABLE SWITCH statement failed. The table 'GBR_Test.dbo.CDR_0_test' has clustered index 'GBR_CDR_0_Test_IX_Xharging_TimeStamp_Partition' while the table 'GBR_Test.dbo.CDR_0_Test_Part' does not have clustered index.
Thanks for your help in advance

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

The error states that the clustered index is different. Is it?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Perry Whittle SSC Guru Points: 234013 More actions · Answer 2

The table 'GBR_Test.dbo.CDR_0_test' has clustered index 'GBR_CDR_0_Test_IX_Xharging_TimeStamp_Partition' while the table 'GBR_Test.dbo.CDR_0_Test_Part' does not have clustered index.

?

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

CrazyMan SSChampion Points: 12560 More actions · Answer 3

Thanks Gail

I have tried with the clustered index as well, but when i tried with all the index that exists on the parent table its not working, now i am getting an error which is as below

Msg 7733, Level 16, State 4, Line 1
'ALTER TABLE SWITCH' statement failed. The table 'GBR_Test.dbo.CDR_0_test' is partitioned while index 'CDR_call_type_Idx' is not partitioned.

But when i went through the partitioning concept its said that partition can be moved to a non partitioned table as well.

I am a bit confused :w00t:

CrazyMan SSChampion Points: 12560 More actions · Answer 4

Thanks Guys

I have done it, I have created the table on the same file group as with the partition and then switched and it worked as a magic

Regards

😀