delete data in a table greater than 24 hours

create PROCEDURE
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.
pborreggAsked:
Who is Participating?
 
JRossi1Commented:
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'




0
 
pborreggAuthor Commented:
Oh yeah, and I need it to run automagically... Please?
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
You need to create a job for this and schedule it to run at 3.00 am
0
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.

 
JRossi1Commented:
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.
0
 
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...

Thanks
0
 
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?
0
 
pborreggAuthor Commented:
0
 
b2mje112Commented:
Keep it a stored procedure, but make it a job like mentioned earlier.
0
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.