delete data in a table greater than 24 hours

dbo.up_CleanUpCadData  AS    DELETE FROM cad_data WHERE DATEDIFF(hh,
cdts, GETDATE()) > 24    

Is a variation of a procedure I found here. It's great but I need it to run at 0300 AM daily.

What modification do I need to make for this to work?

Thanks in advance to all that can assist.
Who is Participating?
1) In Enterprise manager expand the 'Data Transformation Services' folder.

2) Right-Click on 'Local Packages' and select 'New Package'

3) In the 'Connection' menu, select Microsoft OLE DB Provider for SQL Server

4) Set -up the connection to the database

5) Select the 'Task' menu and click on 'Execute SQL Task'

6) Add DELETE FROM cad_data WHERE DATEDIFF(hh,cdts, GETDATE()) > 24   statement to the SQL window

7) Save DTS package

8) Right-Click on package and choose 'Schedule Package'

pborreggAuthor Commented:
Oh yeah, and I need it to run automagically... Please?
Aneesh RetnakaranDatabase AdministratorCommented:
You need to create a job for this and schedule it to run at 3.00 am
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Create a DTS Package with a connection object and an 'Execute SQL Task' object. Add your stored procedure to the Execute SQL task object.  SAve the DTS package.  Now right-click on the package and select 'Schedule Package'.  That's it.
pborreggAuthor Commented:
Guys, I've written Stored Procs before but a DTS Package??? Please expand with an example and where I put this package... Forgive me for being stupid...

pborreggAuthor Commented:
I'm using a ISQL to write this... so do I need another editor or program to write a DTS Package???  Ok a DTS Package is  Data Transfer Service... got it... now how do I write one?
pborreggAuthor Commented:
Keep it a stored procedure, but make it a job like mentioned earlier.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.