Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

90 Day Procedure

Status
Not open for further replies.

mandogon

Programmer
Dec 20, 2007
1
US
Hey people
I have this procedure that does not seem to work what it suppose to do is that after 90 days its suppose to go into the database and delete any data that is over 90 days old i have ran the procedure though interactive sql with no luck i have attached the file so you could take a look at it see if anyone could help me figure out the issue thanks ok dont know how to upload a file ill post the procedure here:


ALTER procedure DBA.Delete90daysOlddata()
//Procedure to delete the Specified days data. This depends in the Column DataArchiveduration from
//tblpsmstoreinformation
begin
declare cur_DaysCalc dynamic scroll cursor for select intDataArchiveDuration from DBA.tblPSMStoreInformation;
declare cntdays integer;
open cur_DaysCalc;
fetch next cur_DaysCalc into cntdays;
delete from DBA.tblItem where UnBigintSumId =
any(select UnBigintSumId from DBA.tblSummary where dtmTransStartDateTime <= GetDate(*)-cntdays);
delete from DBA.tblSupplement where UnBigintSumId =
any(select UnBigintSumId from DBA.tblSummary where dtmTransStartDateTime <= GetDate(*)-cntdays);
delete from DBA.tblTender where UnBigintSumId =
any(select UnBigintSumId from DBA.tblSummary where dtmTransStartDateTime <= GetDate(*)-cntdays);
delete from DBA.tblEvent where UnBigintSumId =
any(select UnBigintSumId from DBA.tblSummary where dtmTransStartDateTime <= GetDate(*)-cntdays);
delete from DBA.tblPriceAdjustment where UnBigintSumId =
any(select UnBigintSumId from DBA.tblSummary where dtmTransStartDateTime <= GetDate(*)-cntdays);
delete from DBA.tblException where UnBigintSumId =
any(select UnBigintSumId from DBA.tblSummary where dtmTransStartDateTime <= GetDate(*)-cntdays);
-- Delete the records from the tblSummary table
delete from DBA.tblSummary where dtmTransStartDateTime <= GetDate(*)-cntdays;
close cur_DaysCalc;
BACKUP DATABASE DIRECTORY '' TRANSACTION LOG ONLY TRANSACTION LOG TRUNCATE;
end

Thanks again
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top