Executing a stored procedure every night

[SilentDeb]

This is very much a 'newbie' question, so please forgive me in advance!

I have a SQL Server 2000 database with a table that needs to be cleared daily of records that are more than 7 days old. I've been doing this manually each day by running a SQL command, but there surely must be a way to automate this.

Could anyone tell me how to run this as a stored procedure that executes at, say, a set time every night? I know this is really a 'you should ask your DBA' type of question, but sadly, I'm the closest thing we have to a DBA ... and as I'm handing the maintenance of this application over to our helpdesk, I don't want to leave any manual maintenance tasks behind, or they likely won't get done (until the application stops working, as it does, when this table gets too large).

Thank you so much!

 

[MeanGreen]

SilentDeb,
You should just set up a job to run every night and paste your query in the job. It sounds like you already have the query, unless you are manually setting some date stuff. An example query would be:

delete mytable
where datecolumn < dateadd(d,-7,getdate())

This would delete all rows where the datecolumn was over 7 days old.

Hope this helps.

 

[SilentDeb]

Hello MeanGreen!

*looks up 'jobs' in the book*

*bangs head on desk*

That does help a lot, bless your heart ... I had NO idea I could do that, I kept trying to figure out how I could get a stored procedure to handle it somehow. THANK YOU! I'll be trying this at work tomorrow!