Partition switching/log purge problem

wiggy353
wiggy353 used Ask the Experts™
on
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:

--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	

Open in new window


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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
lcohanDatabase Analyst

Commented:
My guess is due to some issue with logic and this particular server data to be purged - check out the ProcessData: label is in a BEGIN/END and called right at the begining by code below but @dt is SET = (SELECT $PARTITION.OrderAllocatorLogPF1 (@Purge))
above the ProcessData: label.

if (@MoveArchive>0)
      begin
      Set @reprocess = 1;
      goto ProcessData
      end
Commented:
For some reason it was not accepting the variable. I changed the way the procedure ran to create a string variable that dropped in the other variable and then had it run the string variable as a sql command.

Author

Commented:
I found the solution myself.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial