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