Very odd problem.... - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Very odd problem....

Author

Topic

The1Ash10
Starting Member

15 Posts

Posted - 2009-03-24 : 11:15:03

I have two tables, one that acts as an event log for another software, and the other is simply a table that I want to use for roll-off.

The event log table gets very large very fast, so what I'm trying to do is setup a script that runs every night that will take any data that is older than three days and transfer it to this "roll-off" table.

The problem is as follows:
The event log table keeps time with a Unix time stamp. To deal with this I created this bit of code that seemed like it would be a quick and easy solution:

alter table TABLE1
add SQL_Time datetime;
go

update TABLE1 set sql_time = dateadd(ss,time_stamp,'19700101');
go

insert into TABLE2
select *
from TABLE1
where sql_time <= getdate() - 3;
go

Delete from TABLE1
where sql_time <= getdate() - 3;
go

Right after I add the column titled "SQL_Time" the event log table stops recording the events from the software that it is connected to, and therefore make the table worthless.

My question:
How can I turn the Unix Time Stamp column into something that SQL can read and perform the function that I have outlined above?

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-03-24 : 11:24:11

You could do it without adding a column:


insert into TABLE2
select *
from TABLE1
where dateadd(ss,time_stamp,'19700101') <= getdate() - 3;
go


Delete from TABLE1
where dateadd(ss,time_stamp,'19700101') <= getdate() - 3;
go

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources