[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 317
  • Last Modified:

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.
1 Solution
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
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.
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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.
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'


Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now