Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2009
    Posts
    2

    Unanswered: Very odd problem.... (DBA Skills needed)

    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?

  2. #2
    Join Date
    Mar 2009
    Posts
    2

  3. #3
    Join Date
    Feb 2004
    Posts
    88
    So your problem is that "the event log table stops recording the events from the software that it is connected to" ?

    Does the software that records the events know about the new column ? It may be using an INSERT statement that fails because of the schema change.

    Is your new sql_time column NULL or NOT NULL ? If the latter, then that may also cause problems for the software that's attempting to insert into it...

    I can't see anything wrong with your idea for turning a "unix time stamp" ( presumably an INT data type holding the number of seconds since jan 1 1970) into a SQL date time....

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by The1ash10
    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.
    I would bet the app in question uses the following syntax:
    Code:
    INSERT INTO myTable
    SELECT 'this', 'that', 'the other'
    If you add or remove columns, this syntax breaks - one of the reasons it is not considered best practice.

    Why not merely calculate the date when you need it, rather than store it?

    EDIT - late posting but I'll carry on - thompbil has covered some of my point
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Actually, although I still don't think it is worth storing, you could make it a derived column - then the syntax referred to above would not fail.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •