SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Simple question for some probably...
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mdeligny
Starting Member

USA
10 Posts

Posted - 05/31/2007 :  06:53:07  Show Profile  Reply with Quote
This will probably be VERY easy for some, but since it's all brand new to me I don't know all the variables and codes. I run two simple queries that just deal with two tables. Here is the first one.

Delete from tblCallIndexes where inum in (select inum from tblCalls where StartedAt < '2007-05-27 14:00')

Then I'll run this one:
Delete from tblCalls where StartedAt < '2007-05-27 14:00'

It's basically deleting records by a particular date from two different tables. The problem is, I've been stuck running this manually everyday, and we are not keeping any records older then 48 hours (2 days). Is there anyway I can put these two queries into the system so they run once, twice or maybe even three times a day so constantly keep deleting records up to 48 hours? I can probably figure out where to place it, using Enterprise.

Anyone know how to automate these two queries so I don't have to keep inputting the date manually, but have a batch job going?? I don't care about the contents of the records...all I care about is that when the queries are done, the oldest record is 48 hours old. Any ideas or help on this, possibly easy one? Thank you very much.

By the way....we're using SQL Server 2000 Enterprise.


Michael

Edited by - mdeligny on 05/31/2007 06:54:50

mwjdavidson
Aged Yak Warrior

United Kingdom
735 Posts

Posted - 05/31/2007 :  07:13:45  Show Profile  Reply with Quote
Create a stored proc to do this, using DATEADD(d, -2, GETDATE()) instead of a hard-coded date in your predicate. You can then create a job to execute this stored proc and schedule it to run on a regular basis. Lookup "SQL Agent Job" in BOL.

Mark
Go to Top of Page

mdeligny
Starting Member

USA
10 Posts

Posted - 05/31/2007 :  09:25:45  Show Profile  Reply with Quote
quote:
Originally posted by mwjdavidson

Create a stored proc to do this, using DATEADD(d, -2, GETDATE()) instead of a hard-coded date in your predicate. You can then create a job to execute this stored proc and schedule it to run on a regular basis. Lookup "SQL Agent Job" in BOL.

Mark



I'm still a little hazy....but I assume that when the server restarts this procedure will use an extra connection and be running all the time, correct? A "stored" procedure is just that....stored. What's a procedure called that's been taken out of storage and currently running all the time. A "procedure"? I wasn't sure if a stored procedure just runs automatically because it's there. In our stored procedure list, there must be about 50 procedures there. I take it these are all running, or all have just ran one time? I really need to keep the record count no more then 48 hours. Is there a way to loop this procedure so it keeps running...all the time, or at least every hour or so? I'm still working on my syntax, slowly...but getting there. Thanks.

Go to Top of Page

mwjdavidson
Aged Yak Warrior

United Kingdom
735 Posts

Posted - 05/31/2007 :  10:28:05  Show Profile  Reply with Quote
quote:
this procedure will use an extra connection and be running all the time, correct? A "stored" procedure is just that....stored. What's a procedure called that's been taken out of storage and currently running all the time. A "procedure"?
No, it will only run when explicitly executed (via the EXECUTE command). A stored procedure is just a T-SQL program which is stored within a database. If/when/how often it is executed has no bearing on this. Following your last post, it's a bit clearer what you're trying to achieve, and you definitely don't want to do it like this! Don't delete data from your tables to restrict what is returned when they are queried. Constrain the query using a WHERE clause!

Mark
Go to Top of Page

mdeligny
Starting Member

USA
10 Posts

Posted - 06/02/2007 :  07:32:40  Show Profile  Reply with Quote
quote:
Originally posted by mwjdavidson

quote:
this procedure will use an extra connection and be running all the time, correct? A "stored" procedure is just that....stored. What's a procedure called that's been taken out of storage and currently running all the time. A "procedure"?
No, it will only run when explicitly executed (via the EXECUTE command). A stored procedure is just a T-SQL program which is stored within a database. If/when/how often it is executed has no bearing on this. Following your last post, it's a bit clearer what you're trying to achieve, and you definitely don't want to do it like this! Don't delete data from your tables to restrict what is returned when they are queried. Constrain the query using a WHERE clause!

Mark




You could use a WHERE clause with a dynamic variable like a date? I thought since today's setting would be different then tomorrow's I'd be better off removing the records.

Anyway, it's actually a demand from our legal department (big corporation) that they want NO DATA or anything affiliated with that data older then 24 hours. I'm wondering if using a constraint, wouldn't that still physically hold data for more then 48 hours but just not produce it on returned data? My issue is the data HAS to be removed and deleted. Purged. Some legal issue.
Using the WHERE statement as a constraint is a whole new wotld to me right now. Time to read some more. Thank you.
Go to Top of Page

mwjdavidson
Aged Yak Warrior

United Kingdom
735 Posts

Posted - 06/04/2007 :  04:24:32  Show Profile  Reply with Quote
quote:
You could use a WHERE clause with a dynamic variable like a date?
Yep.
quote:
I thought since today's setting would be different then tomorrow's I'd be better off removing the records.
No, that's where the GETDATE() function comes in.
quote:
Anyway, it's actually a demand from our legal department (big corporation) that they want NO DATA or anything affiliated with that data older then 24 hours.
Sounds kind of unusual. I don't know how often records are being inserted into the table, but it could be nigh on impossible to achieve, even if you run batch deletes almost constantly, which could have serious contention issues.

Mark
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000