February 2, 2012 at 11:02 am
Hi Team,
Using below script we are doing some purge data.
The job was running successfully but the as per logic this sp is not working i think
can you any body please correct me the below scrit any thing wrong.
Thanks in advance.
ALTER
PROCEDURE [dbo].[xxxx] @TableName NVARCHAR (50
)
AS DECLARE @MaxPartitionNumberToPurge int
DECLARE @MinActivePartitionNumber int DECLARE @MinPartitionNumberToPurge int
DECLARE @DaysToKeep int DECLARE @PurgeCommand nvarchar(1024)
DECLARE @TmpPurgeCommand nvarchar(1024) DECLARE @retVal int
DECLARE @PriorPartitionsToPurge Int DECLARE @CurrentRefDate Int
-- Determine the smallest partition number that contains data-- this may not be a candidate to purge - since is may be a day of the current year earlier than the -- number of days to purge - in which case we purge from the prior year only
SELECT @MinActivePartitionNumber = MIN(ps.partition_number
)
FROM sys.dm_db_partition_statsps INNER JOIN sys.partitionsp ON ps.partition_id = p.partition_id AND p.[object_id] = OBJECT_ID(@Tablename) WHERE row_count > 0
-- Fetch the configuration parameters for this purge, plus the Current Date value to fix location in the current yearSELECT @DaysToKeep = DaysToKeep, @PurgeCommand = PurgeCommand, @CurrentRefDate = DATEPART(dayofyear, GETDATE()) FROM dbo.[xxx] WHERE TableName =@TableName-- Partitioning is assumed to be RANGE RIGHT so the partition by DayOfYear. Today's data -- will be to the right by 1 partition. E.g. is dayofyear today is 113, the data is stored-- for today in partition 114. We want to keep at least TODAY - DaysToKeep so compute the
-- maximum partition we should purge UP to. This calculation will handle cases where a daily-- run of the purge was missed (or days missed).
SET @PriorPartitionsToPurge = @DaysToKeep - @CurrentRefDate
-- If we are far enough into the current year to purge from current -- then purge from beginning of the year to days to keep IF @CurrentRefDate > @DaysToKeep
BEGIN SET @MinPartitionNumberToPurge = @MinActivePartitionNumber
SET @MaxPartitionNumberToPurge = @CurrentRefDate - (@DaysToKeep + 1)
END ELSE BEGIN -- If we are less than 'daystokeep' into the current year, then purge only from the prior years partitions SELECT @MinPartitionNumberToPurge = MIN(ps.partition_number) FROM sys.dm_db_partition_statsps INNER JOIN sys.partitionsp ON ps.partition_id = p.partition_idAND p.[object_id] = OBJECT_ID(@Tablename) WHERE row_count >0 AND ps.partition_number > (@CurrentRefDate + 1)
SELECT @MaxPartitionNumberToPurge = MAX(ps.partition_number
)
FROM sys.dm_db_partition_statspsINNER JOIN sys.partitionsp ON ps.partition_id = p.partition_id AND p.[object_id] = OBJECT_ID(@Tablename)WHERE row_count > 0
AND ps.partition_number < (365 - @PriorPartitionsToPurge)END
WHILE (@MinPartitionNumberToPurge <= @MaxPartitionNumberToPurge)BEGIN
SET @TmpPurgeCommand = REPLACE@PurgeCommand,'(PartitionNumberToPurge)',CONVERT(nvarchar(255), @MinPartitionNumberToPurge))
EXEC @retVal = xp_cmdshell @TmpPurgeCommand, no_output;
IF (@retVal = 1) -- Error occured
BEGIN RAISERROR (50001, -- Message id. 16, -- Severity, 1, -- State,
N'xp_cmdshell call in [dbo].[xxx] failed. Purge not complete.'); -- First argument supplies the string. END
SET @MinPartitionNumberToPurge = @MinPartitionNumberToPurge +1 END
-- If we are purging current year - take step to assure that there are no partitions 'above' the current date with left over data that has not been purged
IF @CurrentRefDate >@DaysToKeep BEGIN
SELECT @MinPartitionNumberToPurge = MIN(ps.partition_number)FROM sys.dm_db_partition_statsps INNER JOIN sys.partitionsp ON ps.partition_id = p.partition_id AND p.[object_id] = OBJECT_ID(@Tablename)WHERE row_count > 0
AND ps.partition_number > (@CurrentRefDate + 1)SELECT @MaxPartitionNumberToPurge = MAX(ps.partition_number)FROM sys.dm_db_partition_statsps INNER JOIN sys.partitionsp ON ps.partition_id = p.partition_id AND p.[object_id] = OBJECT_ID(@Tablename)WHERE row_count > 0
AND ps.partition_number > (@CurrentRefDate + 1)
WHILE (@MinPartitionNumberToPurge <= @MaxPartitionNumberToPurge)
BEGIN SET @TmpPurgeCommand = REPLACE(@PurgeCommand,'(PartitionNumberToPurge)',CONVERT(nvarchar(255), @MinPartitionNumberToPurge))
EXEC @retVal = xp_cmdshell @TmpPurgeCommand, no_output;
IF (@retVal = 1) -- Error occured
BEGIN RAISERROR (50001, -- Message id. 16, -- Severity,1, -- State,
N'xp_cmdshell call in [dbo].[xxx] failed. Purge not complete.'); -- First argument supplies the string.END
SET @MinPartitionNumberToPurge = @MinPartitionNumberToPurge + 1
END END RETURN 0
February 2, 2012 at 11:29 am
The code you posted has syntax errors in it.
Msg 156, Level 15, State 1, Procedure xxxx, Line 54
Incorrect syntax near the keyword 'ELSE'.
Msg 102, Level 15, State 1, Procedure xxxx, Line 72
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Procedure xxxx, Line 86
Incorrect syntax near 'xp_cmdshell call in [dbo].[xxx] failed. Purge not complete.'.
Msg 102, Level 15, State 1, Procedure xxxx, Line 128
Incorrect syntax near 'xp_cmdshell call in [dbo].[xxx] failed. Purge not complete.'.
Msg 102, Level 15, State 1, Procedure xxxx, Line 134
Incorrect syntax near '0'.
There are missing brackets, comments where there shouldn't be, spaces missing, etc. Please fix and repost,
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
February 6, 2012 at 2:14 am
Hello ,i think that a problem is you forget to use if statement at first As follow this Rule
IF <CONDITION> BEING
IF <NEW CONDITION> BEGIN
--STATEMENTS
ELSE
--STATEMENTS
END
ELSE
IF <ANOTHER NEW CONDITION> BEGIN
--STATEMENTS
ELSE
IF <ALSO A NEW CONDITION> BEGIN
--STATEMENTS
ELSE
--STATEMENTS
END
END
END
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply