Results 1 to 1 of 1
  1. #1
    Join Date
    May 2010
    Posts
    1

    Unanswered: 90 Day Procedure help

    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:

    <b>
    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
    </b>
    Thanks again
    Last edited by ArmandoGon; 05-05-10 at 17:04. Reason: adding bold

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •