November 11, 2008 at 2:49 pm
ok I have been working on a set of code for archiving information in some of the tables we have and with help got this but now i get this error, not sure why its got an error.
here is the code :
-- archiving the information in the database to speed
-- up the indexing and serching of the table
create proc dbo.ArhicvePunchEvent
(
@CutOffDate datetime=null
)
as
begin
Set nocount on
-- tells the program to check the current time stamp and if less then
-- one year not to archive that data.
if @CutOffDate = DateADD (mm,-12,Current_TimeStamp)
end
-- if the data is older the one year then it moves on
else
begin
if @CutOffDate> DateADD(mm,-12, Current_TimeStamp)
begin
Raiserror('cannot delete order then last 12 months',16,1)
return 1
end
end
-- starts the transaction
begin tran
-- selects all the information in the table for archiving
insert into archive.dbo.punchevents
selet *
from dbo.PUNCHEVENT
where PunchDTM<CutOffDate
-- incase of error well coping info
if @@error <> 70
begin
RollBack Tran
raiserror('error occured while copying data to archive.dbo.punchevent' ,16,1)
return -1
end
-- deletes the old records that have been archived
delete dbo.PUNCHEVENT
where PunchDTM < @CutOffDate
-- incase of error well deleting
if @@error <> 70
begin
rollback tran
raiserror ('error occured while deleting data from dbo.PUNCHEVENT',16,1)
return -1
end
-- begin trans count
if @@TranCount> 0
begin
commit Tran
return 0
end
end
here is the error i am getting:
Msg 156, Level 15, State 1, Procedure ArhicvePunchEvent, Line 15
Incorrect syntax near the keyword 'else'.
can someone please help i know its something stupid like i forgot a punction or something more then likly. Thanks in advance.
November 11, 2008 at 3:09 pm
I'm seeing multiple errors in the code in your post. Can you provide us with what you are trying to accomplish with this procedure? From what I see, I see a lot off work that doesn't necessarily need to be done.
November 12, 2008 at 7:29 am
I am trying to get the proc to go through the information in the table and find any information with a date older then 12 months then take the older information place it in another table for archiving it and delete the old information after it has archive from the first table.
November 12, 2008 at 7:51 am
Please don't cross post. It just wastes people's time and fragments replies.
No more replies to this thread please. Question has already been answered - http://www.sqlservercentral.com/Forums/Topic600722-145-1.aspx
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply