Results 1 to 7 of 7

Thread: Delete rows automatically

  1. #1
    Join Date
    Jul 2003
    Posts
    3

    Delete rows automatically

    Please Help!!!

    I am doing my project and need help urgently.

    Anyone know how to delete a row from database automatically, for eg after 60 days.

    Thank for helping...

  2. #2
    Join Date
    Mar 2003
    Location
    NJ
    Posts
    201
    In Enterprise Manager,schedule a job which will run 60 days later.
    In the steps ,put T-SQL to make the deletion
    In schedule ,let it run one time ,you should specify the date of it.
    Make sure your SQL Server Agent is running under the correct account.(which has access of the task you are going to execute)

  3. #3
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    Claire has given you step by step instructions for SQL Server.

    If you are using any other database, you can use the concept and implement it. Unless you are using Access where you don't have a built in scheduler, in that case you have to write a module and let it start everytime access database is opened, check the date and delete old rows.

    Or write a small program to delete old rows and schedule it on windows scheduler.

  4. #4
    Join Date
    Jul 2003
    Posts
    3
    I'm using Microsoft SQL 2000.
    Can I use ASP codes instaed of SQL(Job Scheduling)?

  5. #5
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    You can. But you need a way to run asp page every day to delete last 60 days data unless you plan to run it manually.

  6. #6
    Join Date
    Jul 2003
    Posts
    3
    There will be a server running it everyday. But I don't know the codes so anyone can help.

  7. #7
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    You need a datetime column in each table you want to delete. This column should contain the insertion/update date for the row.

    Then you can write delete statements for each table like

    delete from table1 where datediff(d,mydate,getdate()) > 60

    where mydate is the column in your table1.

    Or if you want to make it dynamic you can build a cursor with all the user tables and run delete on them.

Posting Permissions

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