We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

delete data in a table greater than 24 hours

pborregg
pborregg asked
on
Medium Priority
344 Views
Last Modified: 2012-06-27
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.
Comment
Watch Question

Author

Commented:
Oh yeah, and I need it to run automagically... Please?
AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

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

Commented:
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.

Author

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

Author

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?

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




Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.