August 11, 2011 at 11:08 am
Hi i got a store procedure which includes switching partitions. If got a failure in in executing store proc.... i should roll back the transaction. If i do, can i get rid of merging and switching the partitions. Will any one help how to handle during error.
This is the following the store proc.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE Procedure [dbo].[PartitionSwap]
@SourceTableName Varchar(50),
@boundary datetime
As
Begin
SET NOCOUNT ON
Declare @SwapTableName Varchar(50), @Stmt Varchar(8000)
SET @SwapTableName = 'SwapTable_' + @SourceTableName
--If exists(select top 10 *from dbo.metadata where (creation_dt)<=dateadd(m,-4,(GETDATE())))
--begin
if exists (select * from sys.partition_range_values where value = @boundary)
BEGIN
--set @stmt= 'truncate table' + @SwapTableName
--exec (@Stmt)
Set @Stmt = 'alter table ' + @SourceTableName +' switch partition ' + Cast($PARTITION.upf_metadata_prtn(@boundary) As Varchar) + ' to '+ @SwapTableName
EXEC(@Stmt)
Set @Stmt= ' Alter Partition function upf_metadata_prtn() merge range ('''+Cast(@boundary as Varchar)+''')'
EXEC(@Stmt)
------ Process_Log table ---------------
Truncate table Process_Log
Insert into Process_Log(Process_Type,Process_date) values('Ars_Data_Oganizer', @boundary)
END
else
begin
Print 'NO Partition'
END
END
August 11, 2011 at 1:21 pm
sqlmaverick (8/11/2011)
Hi i got a store procedure which includes switching partitions. If got a failure in in executing store proc.... i should roll back the transaction. If i do, can i get rid of merging and switching the partitions. Will any one help how to handle during error.
Sorry. I'm not very clear on exactly what you're trying to ask.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply