Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2012 Forums  SQL Server Administration (2012)  error trying to switch out a partition

Author  Topic  sherrer 64 Posts Posted - 2014-01-28 : 11:02:46 We have a situation that data in a partition occasionally gets put into a wrong partition because the partition function was not changed properly. I am trying to develop a script that pulls the data out of the partition, splits the data, change the partition function, then switch back in the split data. I am trying to do this in a way as to only effect the data within the partitions without locking other partitions. Any words or advice would be appreciated. Executing... alter table RecurringBillingRuns switch partition 2 to RecurringBillingRuns1 partition 2, give the following error. Msg 4967, Level 16, State 1, Line 1 ALTER TABLE SWITCH statement failed. SWITCH is not allowed because source table 'Test.dbo.RecurringBillingRuns' contains primary key for constraint 'FK__BillingRuns__RecurringBillingRuns__PartitionKey__RecurringBillingRun_ID'. Here is the structure of both tables involved: CREATE TABLE [dbo].[RecurringBillingRuns]( [Row] [bigint] IDENTITY(1,1) NOT NULL, [ID] [uniqueidentifier] ROWGUIDCOL NOT NULL, [PartitionKey] [bigint] NOT NULL, [Name] [nvarchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [Cycle_ID] [uniqueidentifier] NOT NULL, [RunDate] [int] NOT NULL, [PerformInitialBilling] [bit] NOT NULL, [InitialBillingRangeBegin] [int] NULL, [InitialBillingRangeEnd] [int] NULL, [GenerateFirstReminders] [bit] NOT NULL, [FirstReminderRangeBegin] [int] NULL, [FirstReminderRangeEnd] [int] NULL, [GenerateSecondReminders] [bit] NOT NULL, [SecondReminderRangeBegin] [int] NULL, [SecondReminderRangeEnd] [int] NULL, [GenerateThirdReminders] [bit] NOT NULL, [ThirdReminderRangeBegin] [int] NULL, [ThirdReminderRangeEnd] [int] NULL, [PerformDrops] [bit] NOT NULL, [Mode] [tinyint] NOT NULL, [DropDate] [int] NULL, [TerminationReason_ID] [uniqueidentifier] NULL, [NewStatusForDroppedMembers_ID] [uniqueidentifier] NULL, [Recurrence] [xml] NOT NULL, [ExecutionSchedule] [nvarchar](512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [ExecutionEnds] [nvarchar](512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [StartDate] [datetime] NOT NULL, [NextScheduledRun] [datetime] NULL, [DateLastRun] [datetime] NULL, [Suspended] [bit] NOT NULL, [NumberOfTimesRun] [int] NOT NULL, [ErrorMessage] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [EmailAddresses] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Keywords] [nvarchar](512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [SecurityLock] [xml] NULL, [LockedForDeletion] [bit] NOT NULL, [IsConfiguration] [bit] NOT NULL, [IsSealed] [bit] NOT NULL, [LastModifiedBy_ID] [uniqueidentifier] NOT NULL, [LastModifiedDate] [datetime] NOT NULL, [CreatedBy_ID] [uniqueidentifier] NOT NULL, [CreatedDate] [datetime] NOT NULL, [SystemTimestamp] [timestamp] NOT NULL, CONSTRAINT [PK__RecurringBillingRuns__PartitionKey__ID] PRIMARY KEY NONCLUSTERED ( [PartitionKey] ASC, [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [msMultiTenantSchema]([PartitionKey]) ) ON [msMultiTenantSchema]([PartitionKey]) GO CREATE UNIQUE CLUSTERED INDEX [UC__RecurringBillingRuns__PartitionKey__Row] ON [dbo].[RecurringBillingRuns] ( [PartitionKey] ASC, [Row] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [msMultiTenantSchema]([PartitionKey]) GO CREATE NONCLUSTERED INDEX [IX__RecurringBillingRuns__PartitionKey__Cycle_ID__ForeignKey] ON [dbo].[RecurringBillingRuns] ( [PartitionKey] ASC, [Cycle_ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [msMultiTenantSchema]([PartitionKey]) GO CREATE NONCLUSTERED INDEX [IX__RecurringBillingRuns__PartitionKey__NewStatusForDroppedMembers_ID__ForeignKey] ON [dbo].[RecurringBillingRuns] ( [PartitionKey] ASC, [NewStatusForDroppedMembers_ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [msMultiTenantSchema]([PartitionKey]) GO CREATE NONCLUSTERED INDEX [IX__RecurringBillingRuns__PartitionKey__TerminationReason_ID__ForeignKey] ON [dbo].[RecurringBillingRuns] ( [PartitionKey] ASC, [TerminationReason_ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [msMultiTenantSchema]([PartitionKey]) GO ALTER TABLE [dbo].[RecurringBillingRuns] ADD CONSTRAINT [DF_RecurringBillingRuns_Mode] DEFAULT ((0)) FOR [Mode] GO ALTER TABLE [dbo].[RecurringBillingRuns] WITH CHECK ADD CONSTRAINT [FK__RecurringBillingRuns__BillingCycles__PartitionKey__Cycle_ID] FOREIGN KEY([PartitionKey], [Cycle_ID]) REFERENCES [dbo].[BillingCycles] ([PartitionKey], [ID]) ON UPDATE CASCADE ON DELETE CASCADE GO ALTER TABLE [dbo].[RecurringBillingRuns] CHECK CONSTRAINT [FK__RecurringBillingRuns__BillingCycles__PartitionKey__Cycle_ID] GO ALTER TABLE [dbo].[RecurringBillingRuns] WITH CHECK ADD CONSTRAINT [FK__RecurringBillingRuns__ConfigurableTypes__PartitionKey__TerminationReason_ID] FOREIGN KEY([PartitionKey], [TerminationReason_ID]) REFERENCES [dbo].[ConfigurableTypes] ([PartitionKey], [ID]) GO ALTER TABLE [dbo].[RecurringBillingRuns] CHECK CONSTRAINT [FK__RecurringBillingRuns__ConfigurableTypes__PartitionKey__TerminationReason_ID] GO ALTER TABLE [dbo].[RecurringBillingRuns] WITH CHECK ADD CONSTRAINT [FK__RecurringBillingRuns__MembershipStatuses__PartitionKey__NewStatusForDroppedMembers_ID] FOREIGN KEY([PartitionKey], [NewStatusForDroppedMembers_ID]) REFERENCES [dbo].[MembershipStatuses] ([PartitionKey], [ID]) GO ALTER TABLE [dbo].[RecurringBillingRuns] CHECK CONSTRAINT [FK__RecurringBillingRuns__MembershipStatuses__PartitionKey__NewStatusForDroppedMembers_ID] GO ALTER TABLE [dbo].[RecurringBillingRuns] WITH CHECK ADD CONSTRAINT [CK_RecurringBillingRuns_Drops] CHECK (([PerformDrops]=(0) OR [DropDate] IS NOT NULL)) GO ALTER TABLE [dbo].[RecurringBillingRuns] CHECK CONSTRAINT [CK_RecurringBillingRuns_Drops] GO ALTER TABLE [dbo].[RecurringBillingRuns] WITH CHECK ADD CONSTRAINT [CK_RecurringBillingRuns_FirstReminder] CHECK (([GenerateFirstReminders]=(0) OR [FirstReminderRangeBegin] IS NOT NULL AND [FirstReminderRangeEnd] IS NOT NULL)) GO ALTER TABLE [dbo].[RecurringBillingRuns] CHECK CONSTRAINT [CK_RecurringBillingRuns_FirstReminder] GO ALTER TABLE [dbo].[RecurringBillingRuns] WITH CHECK ADD CONSTRAINT [CK_RecurringBillingRuns_InitialBilling] CHECK (([PerformInitialBilling]=(0) OR [InitialBillingRangeBegin] IS NOT NULL AND [InitialBillingRangeEnd] IS NOT NULL)) GO ALTER TABLE [dbo].[RecurringBillingRuns] CHECK CONSTRAINT [CK_RecurringBillingRuns_InitialBilling] GO ALTER TABLE [dbo].[RecurringBillingRuns] WITH CHECK ADD CONSTRAINT [CK_RecurringBillingRuns_SecondReminder] CHECK (([GenerateSecondReminders]=(0) OR [SecondReminderRangeBegin] IS NOT NULL AND [SecondReminderRangeEnd] IS NOT NULL)) GO ALTER TABLE [dbo].[RecurringBillingRuns] CHECK CONSTRAINT [CK_RecurringBillingRuns_SecondReminder] GO ALTER TABLE [dbo].[RecurringBillingRuns] WITH CHECK ADD CONSTRAINT [CK_RecurringBillingRuns_ThirdReminder] CHECK (([GenerateThirdReminders]=(0) OR [ThirdReminderRangeBegin] IS NOT NULL AND [ThirdReminderRangeEnd] IS NOT NULL)) GO ALTER TABLE [dbo].[RecurringBillingRuns] CHECK CONSTRAINT [CK_RecurringBillingRuns_ThirdReminder] GO CREATE TABLE [dbo].[BillingRuns]( [Row] [bigint] IDENTITY(1,1) NOT NULL, [ID] [uniqueidentifier] ROWGUIDCOL NOT NULL, [PartitionKey] [bigint] NOT NULL, [Name] [nvarchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [Cycle_ID] [uniqueidentifier] NOT NULL, [Batch_ID] [uniqueidentifier] NULL, [RunDate] [datetime] NOT NULL, [Status] [tinyint] NOT NULL, [RecurringBillingRun_ID] [uniqueidentifier] NULL, [PerformInitialBilling] [bit] NOT NULL, [InitialBillingRangeBegin] [date] NULL, [InitialBillingRangeEnd] [date] NULL, [GenerateFirstReminders] [bit] NOT NULL, [FirstReminderRangeBegin] [date] NULL, [FirstReminderRangeEnd] [date] NULL, [GenerateSecondReminders] [bit] NOT NULL, [SecondReminderRangeBegin] [date] NULL, [SecondReminderRangeEnd] [date] NULL, [GenerateThirdReminders] [bit] NOT NULL, [ThirdReminderRangeBegin] [date] NULL, [ThirdReminderRangeEnd] [date] NULL, [PerformDrops] [bit] NOT NULL, [ErrorMessage] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Mode] [tinyint] NOT NULL, [DropDate] [date] NULL, [TerminationReason_ID] [uniqueidentifier] NULL, [NewStatusForDroppedMembers_ID] [uniqueidentifier] NULL, [DateScheduled] [datetime] NULL, [DateStarted] [datetime] NULL, [DateCompleted] [datetime] NULL, [Keywords] [nvarchar](512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [SecurityLock] [xml] NULL, [LockedForDeletion] [bit] NOT NULL, [IsConfiguration] [bit] NOT NULL, [IsSealed] [bit] NOT NULL, [LastModifiedBy_ID] [uniqueidentifier] NOT NULL, [LastModifiedDate] [datetime] NOT NULL, [CreatedBy_ID] [uniqueidentifier] NOT NULL, [CreatedDate] [datetime] NOT NULL, [SystemTimestamp] [timestamp] NOT NULL, CONSTRAINT [PK__BillingRuns__PartitionKey__ID] PRIMARY KEY NONCLUSTERED ( [PartitionKey] ASC, [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [msMultiTenantSchema]([PartitionKey]) ) ON [msMultiTenantSchema]([PartitionKey]) GO CREATE UNIQUE CLUSTERED INDEX [UC__BillingRuns__PartitionKey__Row] ON [dbo].[BillingRuns] ( [PartitionKey] ASC, [Row] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [msMultiTenantSchema]([PartitionKey]) GO CREATE NONCLUSTERED INDEX [IX__BillingRuns__PartitionKey__Batch_ID__ForeignKey] ON [dbo].[BillingRuns] ( [PartitionKey] ASC, [Batch_ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [msMultiTenantSchema]([PartitionKey]) GO CREATE NONCLUSTERED INDEX [IX__BillingRuns__PartitionKey__Cycle_ID__ForeignKey] ON [dbo].[BillingRuns] ( [PartitionKey] ASC, [Cycle_ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [msMultiTenantSchema]([PartitionKey]) GO CREATE NONCLUSTERED INDEX [IX__BillingRuns__PartitionKey__NewStatusForDroppedMembers_ID__ForeignKey] ON [dbo].[BillingRuns] ( [PartitionKey] ASC, [NewStatusForDroppedMembers_ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [msMultiTenantSchema]([PartitionKey]) GO CREATE NONCLUSTERED INDEX [IX__BillingRuns__PartitionKey__RecurringBillingRun_ID__ForeignKey] ON [dbo].[BillingRuns] ( [PartitionKey] ASC, [RecurringBillingRun_ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [msMultiTenantSchema]([PartitionKey]) GO CREATE NONCLUSTERED INDEX [IX__BillingRuns__PartitionKey__TerminationReason_ID__ForeignKey] ON [dbo].[BillingRuns] ( [PartitionKey] ASC, [TerminationReason_ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [msMultiTenantSchema]([PartitionKey]) GO ALTER TABLE [dbo].[BillingRuns] ADD CONSTRAINT [DF_BillingRuns_Status] DEFAULT ((0)) FOR [Status] GO ALTER TABLE [dbo].[BillingRuns] ADD CONSTRAINT [DF_BillingRuns_Mode] DEFAULT ((0)) FOR [Mode] GO ALTER TABLE [dbo].[BillingRuns] WITH CHECK ADD CONSTRAINT [FK__BillingRuns__Batches__PartitionKey__Batch_ID] FOREIGN KEY([PartitionKey], [Batch_ID]) REFERENCES [dbo].[Batches] ([PartitionKey], [ID]) GO ALTER TABLE [dbo].[BillingRuns] CHECK CONSTRAINT [FK__BillingRuns__Batches__PartitionKey__Batch_ID] GO ALTER TABLE [dbo].[BillingRuns] WITH CHECK ADD CONSTRAINT [FK__BillingRuns__BillingCycles__PartitionKey__Cycle_ID] FOREIGN KEY([PartitionKey], [Cycle_ID]) REFERENCES [dbo].[BillingCycles] ([PartitionKey], [ID]) ON UPDATE CASCADE ON DELETE CASCADE GO ALTER TABLE [dbo].[BillingRuns] CHECK CONSTRAINT [FK__BillingRuns__BillingCycles__PartitionKey__Cycle_ID] GO ALTER TABLE [dbo].[BillingRuns] WITH CHECK ADD CONSTRAINT [FK__BillingRuns__ConfigurableTypes__PartitionKey__TerminationReason_ID] FOREIGN KEY([PartitionKey], [TerminationReason_ID]) REFERENCES [dbo].[ConfigurableTypes] ([PartitionKey], [ID]) GO ALTER TABLE [dbo].[BillingRuns] CHECK CONSTRAINT [FK__BillingRuns__ConfigurableTypes__PartitionKey__TerminationReason_ID] GO ALTER TABLE [dbo].[BillingRuns] WITH CHECK ADD CONSTRAINT [FK__BillingRuns__MembershipStatuses__PartitionKey__NewStatusForDroppedMembers_ID] FOREIGN KEY([PartitionKey], [NewStatusForDroppedMembers_ID]) REFERENCES [dbo].[MembershipStatuses] ([PartitionKey], [ID]) GO ALTER TABLE [dbo].[BillingRuns] CHECK CONSTRAINT [FK__BillingRuns__MembershipStatuses__PartitionKey__NewStatusForDroppedMembers_ID] GO ALTER TABLE [dbo].[BillingRuns] WITH CHECK ADD CONSTRAINT [FK__BillingRuns__RecurringBillingRuns__PartitionKey__RecurringBillingRun_ID] FOREIGN KEY([PartitionKey], [RecurringBillingRun_ID]) REFERENCES [dbo].[RecurringBillingRuns] ([PartitionKey], [ID]) GO ALTER TABLE [dbo].[BillingRuns] CHECK CONSTRAINT [FK__BillingRuns__RecurringBillingRuns__PartitionKey__RecurringBillingRun_ID] GO ALTER TABLE [dbo].[BillingRuns] WITH CHECK ADD CONSTRAINT [CK_BillingRuns_Drops] CHECK (([PerformDrops]=(0) OR [DropDate] IS NOT NULL)) GO ALTER TABLE [dbo].[BillingRuns] CHECK CONSTRAINT [CK_BillingRuns_Drops] GO ALTER TABLE [dbo].[BillingRuns] WITH CHECK ADD CONSTRAINT [CK_BillingRuns_FirstReminder] CHECK (([GenerateFirstReminders]=(0) OR [FirstReminderRangeBegin] IS NOT NULL AND [FirstReminderRangeEnd] IS NOT NULL)) GO ALTER TABLE [dbo].[BillingRuns] CHECK CONSTRAINT [CK_BillingRuns_FirstReminder] GO ALTER TABLE [dbo].[BillingRuns] WITH CHECK ADD CONSTRAINT [CK_BillingRuns_InitialBilling] CHECK (([PerformInitialBilling]=(0) OR [InitialBillingRangeBegin] IS NOT NULL AND [InitialBillingRangeEnd] IS NOT NULL)) GO ALTER TABLE [dbo].[BillingRuns] CHECK CONSTRAINT [CK_BillingRuns_InitialBilling] GO ALTER TABLE [dbo].[BillingRuns] WITH CHECK ADD CONSTRAINT [CK_BillingRuns_SecondReminder] CHECK (([GenerateSecondReminders]=(0) OR [SecondReminderRangeBegin] IS NOT NULL AND [SecondReminderRangeEnd] IS NOT NULL)) GO ALTER TABLE [dbo].[BillingRuns] CHECK CONSTRAINT [CK_BillingRuns_SecondReminder] GO ALTER TABLE [dbo].[BillingRuns] WITH CHECK ADD CONSTRAINT [CK_BillingRuns_ThirdReminder] CHECK (([GenerateThirdReminders]=(0) OR [ThirdReminderRangeBegin] IS NOT NULL AND [ThirdReminderRangeEnd] IS NOT NULL)) GO ALTER TABLE [dbo].[BillingRuns] CHECK CONSTRAINT [CK_BillingRuns_ThirdReminder] GO