asked on

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.

pborregg

ASKER

Oh yeah, and I need it to run automagically... Please?

Aneesh

You need to create a job for this and schedule it to run at 3.00 am

JRossi1

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.

pborregg

ASKER

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

pborregg

ASKER

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?

pborregg

ASKER

OK, use enterprise manager right???

http://www.devarticles.com/c/a/ASP/Creating-DTS-Packages-With-SQL-Server-2000/1/

b2mje112

Keep it a stored procedure, but make it a job like mentioned earlier.

ASKER CERTIFIED SOLUTION

JRossi1

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial