November 17, 2011 at 5:12 am
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
SELECT @MinActivePartitionNumber = MIN(ps.partition_number)
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] = OBJECT_ID(@Tablename)
WHERE row_count > 0
SELECT @DaysToKeep = DaysToKeep, @PurgeCommand = PurgeCommand, @CurrentRefDate = DATEPART(dayofyear, GETDATE())
FROM dbo.[Config]
WHERE TableName = @TableName
SET @PriorPartitionsToPurge = @DaysToKeep - @CurrentRefDate
IF @CurrentRefDate > @DaysToKeep
BEGIN
SET @MinPartitionNumberToPurge = @MinActivePartitionNumber
SET @MaxPartitionNumberToPurge = @CurrentRefDate - (@DaysToKeep + 1)
END
ELSE
BEGIN
SELECT @MinPartitionNumberToPurge = MIN(ps.partition_number)
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
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_stats ps
INNER JOIN sys.partitions p
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 (N'xp_cmdshell call in [dbo].[adminManagePartition_Trace] failed. Purge not complete.',
16, -- Severity,
1 -- State,
);
--RAISERROR (N'xp_cmdshell call in [dbo].[adminManagePartition_Trace] failed. Purge not complete.',
--16, -- Severity,
--1, -- State,
--RAISERROR (50001, -- Message id.
-- 16, -- Severity,
--1, -- State,
--N'xp_cmdshell call in [dbo].[adminManagePartition_Trace] failed. Purge not complete.'); -- First argument supplies the string.
END
-- Print 'Min:'
-- Print @MinPartitionNumberToPurge
-- Print 'Max:'
-- Print @MaxPartitionNumberToPurge
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_stats ps
INNER JOIN sys.partitions p
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_stats ps
INNER JOIN sys.partitions p
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 (N'xp_cmdshell call in [dbo].[adminManagePartition_Trace] failed. Purge not complete.',
16, -- Severity,
1 -- State,
);
--RAISERROR (50001, -- Message id.
--16, -- Severity,
-- 1, -- State,
-- N'xp_cmdshell call in [dbo].[adminManagePartition_Trace] failed. Purge not complete.'); -- First argument supplies the string.
END
-- Print 'Min:'
-- Print @MinPartitionNumberToPurge
-- Print 'Max:'
-- Print @MaxPartitionNumberToPurge
SET @MinPartitionNumberToPurge = @MinPartitionNumberToPurge + 1
END
END
RETURN 0
November 17, 2011 at 5:17 am
Here is your clue:
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].[adminManagePartition_Trace] failed. Purge not complete.'); -- First argument supplies the string.
END
Try running the xp_cmdshell command in a query window without the no_output option.
John
November 17, 2011 at 5:21 am
The error's pretty clear. You're trying to throw error 50001, but no such error message exists.
You either need to add that message to sys.messages, then you can call Raiserror with a number, or you need to use the alternative Raiserror sybtax without a number
RAISERROR (N'xp_cmdshell call in [dbo].[xxx_Trace] failed. Purge not complete.',
16, -- Severity,
1, -- State,
);
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
November 17, 2011 at 5:32 am
GilaMonster (11/17/2011)
The error's pretty clear. You're trying to throw error 50001, but no such error message exists.You either need to add that message to sys.messages, then you can call Raiserror with a number, or you need to use the alternative Raiserror sybtax without a number
RAISERROR (N'xp_cmdshell call in [dbo].[xxx_Trace] failed. Purge not complete.',
16, -- Severity,
1, -- State,
);
Gail
I told the OP exactly the same thing 2 days ago.
http://www.sqlservercentral.com/Forums/Topic1205123-1550-1.aspx#bm1205758
[font="Verdana"]Markus Bohse[/font]
November 17, 2011 at 5:43 am
Hi Gail,
we can add error right
what kind of error we have to write there
and we add these errors in sys.sysmessages, using sp_addmessges
what i can add. please suggest me.
Tx
November 17, 2011 at 5:53 am
put sp_addmessage into BOL, google etc which will give you the syntax
then create a message which has meaning to you
November 17, 2011 at 6:14 am
Hi Gail,
As per your suggestion only i have created
RAISERROR (N'xp_cmdshell call in [dbo].[xxxx_Trace] failed. Purge not complete.',
16, -- Severity,
1 -- State,
);
but i m getting below Error please guide me.
xp_cmdshell call in [dbo].[xxxx_Trace] failed. Purge not complete. [SQLSTATE 42000] (Error 50000)
better we have to add new messages how can i add messagae please as per my logic
to be honestly i m not understand the code
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply