Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mdeligny
Starting Member
10 Posts |
 Posted - 2007-05-31 : 06:53:07
|
| 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 |
|
|
mwjdavidson
Aged Yak Warrior
735 Posts |
Posted - 2007-05-31 : 07:13:45
|
| 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 |
 |
|
|
mdeligny
Starting Member
10 Posts |
Posted - 2007-05-31 : 09:25:45
|
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. |
|
|
|
mwjdavidson
Aged Yak Warrior
735 Posts |
Posted - 2007-05-31 : 10:28:05
|
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 |
|
|
|
mdeligny
Starting Member
10 Posts |
Posted - 2007-06-02 : 07:32:40
|
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. |
|
|
|
mwjdavidson
Aged Yak Warrior
735 Posts |
Posted - 2007-06-04 : 04:24:32
|
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 |
|
|
|
|
|
|
|
|
Simple question for some probably...