wiggy353
asked on
Partition switching/log purge problem
The senior sql people at my work are not here today and I need to figure out why this job keeps failing. It has to do with partition switching and I am not very familiar with that.
Here is the code from the stored procedure that fails:
When this runs, it gives an error that the scalar variable @dt must be declared. But from what I can see it is being declared. The exact point that the job fails at is line 82, when it tries to ALTER TABLE and switch the partition.
Can someone help me understand what the problem is? We have this same job running on other servers and it works fine.
Thanks
Here is the code from the stored procedure that fails:
--all tables created
Declare
@ArchiveCount as bigint
, @MoveArchive as bigint
, @totalCount as bigint
Declare
@AfterArchiveCount as bigint
, @AfterMoveArchive as bigint
, @AftertotalCount as bigint
Declare
@dt as int
, @Purge as tinyint
, @ct as bigint
, @reprocess as int
Declare
@DaystoKeep as int
Set @DaystoKeep = 15
Set @reprocess = 0;
-- check to see if table is not empty
Set @MoveArchive = (select count(*) from Purge.OrderAllocatorLog)
if (@MoveArchive>0)
begin
Set @reprocess = 1;
goto ProcessData
end
FinishProcess:
Set @Purge = (Select datepart(day,getdate()))
Set @Purge = @Purge - 1
If (@Purge = 0)
Set @Purge = 31;
Set @ct = (select count(*) from dbo.OrderAllocatorLog where Purge = @Purge)
While (@ct = 0)
Begin
Set @Purge = @Purge - 1
Set @ct = (select count(*) from dbo.OrderAllocatorLog where Purge = @Purge)
if (@Purge = 27)
Begin
Set @ct = -1
Set @Purge = 99
End
If ((@ct = 0) and (@Purge =0))
Goto EndNow
End
if ((@Purge > 0) and (@Purge < 32))
Begin
set @dt = (SELECT $PARTITION.OrderAllocatorLogPF1 (@Purge))
--select @dt
Truncate table Purge.OrderAllocatorLog
ALTER TABLE dbo.OrderAllocatorLog SWITCH PARTITION @dt TO Purge.OrderAllocatorLog
ProcessData:
Set @ArchiveCount = (select count(*) from dbo.OrderAllocatorLogArchive)
Set @MoveArchive = (select count(*) from Purge.OrderAllocatorLog)
Set @totalCount = @ArchiveCount + @MoveArchive
if (@MoveArchive > 0)
Begin
INSERT INTO [CMOP].[dbo].[OrderAllocatorLogArchive]
([ID],[DateAndTime],[MessageText])
Select [ID],[DateAndTime],[MessageText]
from Purge.OrderAllocatorLog
Set @AfterArchiveCount = (select count(*) from dbo.OrderAllocatorLogArchive)
If (@AfterArchiveCount <> @totalCount)
Begin
INSERT INTO [CMOP].[dbo].[OrderAllocatorLogArchive]
([ID],[DateAndTime],[MessageText])
Select oal.[ID],oal.[DateAndTime],oal.[MessageText]
from Purge.OrderAllocatorLog oal
left join [dbo].[OrderAllocatorLogArchive] oala
on oal.ID = oala.ID
and oal.DateAndTime = oala.DateAndTime
where oala.ID is null
End
Else IF (@AfterArchiveCount = @totalCount)
Begin
Truncate table Purge.OrderAllocatorLog
End
End
end
if (@reprocess = 1)
Begin
set @reprocess = 0;
goto FinishProcess
End
EndNow:
Delete from [dbo].[OrderAllocatorLogArchive]
where DateAndTime < getdate()- @DaystoKeep
When this runs, it gives an error that the scalar variable @dt must be declared. But from what I can see it is being declared. The exact point that the job fails at is line 82, when it tries to ALTER TABLE and switch the partition.
Can someone help me understand what the problem is? We have this same job running on other servers and it works fine.
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I found the solution myself.
above the ProcessData: label.
if (@MoveArchive>0)
begin
Set @reprocess = 1;
goto ProcessData
end