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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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
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:
b2mje112Commented:
Keep it a stored procedure, but make it a job like mentioned earlier.
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'




Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.