Yes
Something like:
Suppost table is INVOICE
Log table is INVOICELOG, with all columns in INVOICE Plus InvoiceLogBKUP
DATETIME
-- Insert new rows
CREATE TEMP TABLE zInvoice (same cols as INVOICE)
INSERT INTO zINVOICE SELECT (list columns) FROM INVOICELOG WHERE
InvoiceLogBKUP > xxxx (last date/time stamp you want to restore from) AND
InvoiceID NOT IN (SELECT InvoiceID from INVOICE)
INSERT INTO INVOICE SEL * FROM zInvoice
-- Update changed rows
DECLARE C1 CURSOR FOR SELECT * from INVOICELOG WHERE InvoiceLogBKUP > xxxx
(last date/time stamp you want to restore from) AND InvoiceID IN (SELECT
InvoiceID from INVOICE)
OPEN C1
WHILE #PI <> 0 THEN
FETCH C1 INTO (one var for each column in INVOICELOG except
InvoiceLogBKUP)
IF SQLCODE = 100 THEN
BREAK
ENDIF
-- This works for the master table; for sub tables where there could be
more
-- than one row for each InvoiceID, you'll need a way to identify the
unique
-- row to be matched to and updated
UPDATE INVOICE SET 1stcolumn = .v1stcolumn, 2ndcolumn = .v2ndcolumn ETC
WHERE InvoiceID = .vinvoiceID
ENDWHILE
David Blocker
[EMAIL PROTECTED]
781-784-1919
Fax: 781-784-1860
Cell: 339-206-0261
----- Original Message -----
From: "Claudine Robbins" <[EMAIL PROTECTED]>
To: "RBG7-L Mailing List" <[email protected]>
Sent: Monday, March 21, 2005 9:34 AM
Subject: [RBG7-L] - RE: Access Violation Messages and Sys_Layouts3
tablecorruption
> OK, David, my mind doesn't want to get around your second sentence easily
> this morning...
>
> The log would have to be of 9 tables, so 9 logs... All the tables are
> related by invoice_id. So, what you're saying is table by table -- cursor
> loop? -- check each log entry for changes between the backup and restored
> dbs based on the update timestamp?
>
> I'll definitely look into this as well as making more incremental backups
> during the day.
>
> Thanks David.
>
> Claudine
>
> > -----Original Message-----
> > From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of David M.
> > Blocker
> > Sent: Monday, March 21, 2005 7:59 AM
> > To: RBG7-L Mailing List
> > Subject: [RBG7-L] - RE: Access Violation Messages and Sys_Layouts3
> > tablecorruption
> >
> > Claudine
> >
> > Why not use Bernie's system of a log of changed / inserted rows in
> > combination with your table?
> >
> > If your table to be updated as a PK column TablePK, and the update
record
> > table has ALL the columns from the main table, with copies of the data
as
> > of
> > the date of change/insert PLUS the date/time of insert/update, you could
> > find the rows that have been inserted/changed after the date / time of
the
> > database which you are restoring from, and insert the new rows and
update
> > the changed ones from the backup table data.
> >
> > David
> >
> >
> > David Blocker
> > [EMAIL PROTECTED]
> > 781-784-1919
> > Fax: 781-784-1860
> > Cell: 339-206-0261
> > ----- Original Message -----
> > From: "Claudine Robbins" <[EMAIL PROTECTED]>
> > To: "RBG7-L Mailing List" <[email protected]>
> > Sent: Sunday, March 20, 2005 8:21 PM
> > Subject: [RBG7-L] - RE: Access Violation Messages and Sys_Layouts3 table
> > corruption
> >
> >
> > > Alastair,
> > >
> > > I know Murphy's law (if something can go wrong, it will) but I've
never
> > > heard of Sod's... Good point on the temp tables, I have 9 on my main
> > form
> > > for each record.
> > >
> > > Thanks for sharing :)
> > >
> > > > -----Original Message-----
> > > > From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of
Alastair
> > > > Burr
> > > > Sent: Sunday, March 20, 2005 6:09 PM
> > > > To: RBG7-L Mailing List
> > > > Subject: [RBG7-L] - RE: Access Violation Messages and Sys_Layouts3
> > table
> > > > corruption
> > > >
> > > > Claudine, there's only one thing that you can be absolutely sure of
> > with
> > > > backups: overkill will never be enough!
> > > >
> > > > Murphy and Sod have their laws and you can't beat them - all you can
> > do
> > is
> > > > copy, backup, reload, unload - whatever suits you best - as often as
> > you
> > > > can
> > > > and then some.
> > > >
> > > > Whatever you do also be aware, if you use them, of temp tables being
> > lost
> > > > if
> > > > you can. I enter a lot of data via temp tables but don't append it
to
> > the
> > > > main table until all is verified so I unload the temp data to a file
> > and
> > > > overwrite the file periodically. Of course, that point will be the
> > precise
> > > > time that the worst happens so paranoia is part of Messrs Murphy and
> > Sod's
> > > > legacy.
> > > >
> > > > Good luck,
> > > > Regards,
> > > > Alastair.
> > > >
> > > >
> > > > ----- Original Message -----
> > > > From: "Claudine Robbins" <[EMAIL PROTECTED]>
> > > > To: "RBG7-L Mailing List" <[email protected]>
> > > > Sent: Sunday, March 20, 2005 9:17 PM
> > > > Subject: [RBG7-L] - RE: Access Violation Messages and Sys_Layouts3
> > table
> > > > corruption
> > > >
> > > >
> > > > > Bernie,
> > > > >
> > > > > In my transportation database, I write/append to an audit file
each
> > time
> > > > a
> > > > > record is added or edited, but I'm only dealing with one table. I
> > also
> > > > have
> > > > > an incremental backup several times during the day from which I
can
> > > > easily
> > > > > restore. In my invoices database, there are 9 related tables.
> > Writing
> > > > the
> > > > > data is simple enough, restoring it might be complex.
> > > > >
> > > > > I think I need to take a Backup 101 course. Backup and
> > documentation
> > > > always
> > > > > seem last on the to-do list... With the Oracle database -- now
> > > > successfully
> > > > > replaced with RBase :) -- we had a sophisticated daily backup to
> > tape
> > in
> > > > > place along the son/father/grandfather model. Not one single
Oracle
> > dba
> > > > was
> > > > > ever willing to assure me that any of it could successfully be
> > > > restored...
> > > > > And to my knowledge, it was never even tested!!!
> > > > >
> > > > > Let's hear it from the list... What's everybody doing for backups?
> > > > >
> > > > > Claudine
> > > > >
> > > > > > -----Original Message-----
> > > > > > From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of
> > Bernard
> > > > Lis
> > > > > > Sent: Sunday, March 20, 2005 2:03 PM
> > > > > > To: RBG7-L Mailing List
> > > > > > Subject: [RBG7-L] - RE: Access Violation Messages and
Sys_Layouts3
> > > > table
> > > > > > corruption
> > > > > >
> > > > > > Hi Claudine,
> > > > > > For one of my customers, I have a tracking table.
> > > > > > Any time an item is added to the item table, or a sale is made,
or
> > > > > > inventory
> > > > > > is replenished, a row is added to the tracking table which
> > indicates
> > > > any
> > > > > > of
> > > > > > the above actions and the date and time it ocurred, and
WhoDidIt.
> > > > > > Though we haven't needed to use it for rebuilding purposes, the
> > store
> > > > > > manager uses it to determine why inventory is not what it is
> > supposed
> > > > to
> > > > > > be
> > > > > > and if any midnight requisitioning is happening.
> > > > > >
> > > > > > Bernie Lis
> > > > > >
> > > > > > ----- Original Message -----
> > > > > > From: "Claudine Robbins" <[EMAIL PROTECTED]>
> > > > > > To: "RBG7-L Mailing List" <[email protected]>
> > > > > > Sent: Sunday, March 20, 2005 1:24 PM
> > > > > > Subject: [RBG7-L] - RE: Access Violation Messages and
Sys_Layouts3
> > > > table
> > > > > > corruption
> > > > > >
> > > > > >
> > > > > > > Ok gang, here is what I did to mitigate my problem. It's not
a
> > > > complete
> > > > > > > solution but I believe the surgery was the least invasive for
> > the
> > > > > > patient.
> > > > > > >
> > > > > > > My backup from Thursday night when restored, didn't cause any
> > access
> > > > > > > violations :). However, it did trip RScope with that minor
> > > > sys_layouts3
> > > > > > > problem which I fixed simply by deleting the two rows causing
> > the
> > > > > > problem
> > > > > > > and reloading the only row unloaded from the oldest backup
which
> > > > didn't
> > > > > > > have
> > > > > > > that problem. As Ross Perot used to say: "Are you with me?"
> > > > > > >
> > > > > > > That left me with having to restore data entered on Friday
which
> > I
> > > > > > > unloaded
> > > > > > > from the damaged db and reloaded into the corrected db. The
> > only
> > > > thing
> > > > > > I
> > > > > > > don't know how to do is to restore changes made on Friday to
> > > > existing
> > > > > > data
> > > > > > > which I guess will simply have to be re-entered. All in all,
it
> > > > should
> > > > > > > work.
> > > > > > >
> > > > > > > Thank goodness for daily backups. But a question begs, how
> > would
> > > > you
> > > > > > > backup/restore changes made to existing data? Some kind of a
> > > > trigger?
> > > > > > I
> > > > > > > need an education on this subject now that I've had the hard
> > > > lesson...
> > > > > > >
> > > > > > > Claudine
> > > > > > >
> > > > > > >> -----Original Message-----
> > > > > > >> From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of
> > Sami
> > > > > > Aaron
> > > > > > >> Sent: Saturday, March 19, 2005 1:43 PM
> > > > > > >> To: RBG7-L Mailing List
> > > > > > >> Subject: [RBG7-L] - RE: Access Violation Messages and
> > Sys_Layouts3
> > > > > > table
> > > > > > >> corruption
> > > > > > >>
> > > > > > >> Claudine -
> > > > > > >>
> > > > > > >> There are a couple more options:
> > > > > > >>
> > > > > > >> 1. Find a backup copy of the rb1 file from before the
> > corruption
> > > > > > >> occurred.
> > > > > > >> (NOTE: this will not work if you have made any structural
> > changes
> > > > to
> > > > > > the
> > > > > > >> database since the date/time on the rb1 file.) Copy the rb1
> > file
> > > > into
> > > > > > >> your
> > > > > > >> working folder, replacing the existing rb1 file. Now, do a
> > reload
> > > > on
> > > > > > the
> > > > > > >> database and see if that helps.
> > > > > > >>
> > > > > > >> 2. Unload all for each of your database tables separately
> > like:
> > > > > > >> SET NULL -0-
> > > > > > >> OUTPUT IHopeThisWorks.str
> > > > > > >> UNLOAD str
> > > > > > >> OUTPUT SCREEN
> > > > > > >> OUTPUT IHopeThisWorks.dta
> > > > > > >> UNLOAD DATA
> > > > > > >> OUTPUT SCREEN
> > > > > > >>
> > > > > > >> Then, RBEDIT IHopeThisWorks.str and search for "CREATE VIEW".
> > > > > > >> Split this file into two files - everything ABOVE the first
> > CREATE
> > > > VIEW
> > > > > > >> statement into IHopeThisWorks1.str, and everything from the
> > first
> > > > > > CREATE
> > > > > > >> VIEW to the end of the file as IHopeThisWorks2.str.
> > > > > > >>
> > > > > > >> Then, RBEDIT IHopeThisWorks.dta and search for SYS_Layouts
and
> > > > delete
> > > > > > the
> > > > > > >> whole LOAD section for that table.
> > > > > > >>
> > > > > > >> Then, get rid of the original database or rename it to
> > something
> > > > else.
> > > > > > >>
> > > > > > >> SET MESS ON
> > > > > > >> SET ERROR MESS ON
> > > > > > >> TRACE IHopeThisWorks1.str
> > > > > > >> --If no errors then
> > > > > > >> RUN IHopeThisWorks.dta
> > > > > > >> --If no errors then
> > > > > > >> TRACE IHopeThisWorks2.str
> > > > > > >>
> > > > > > >> If that still doesn't work, then I still have one more way up
> > my
> > > > sleeve
> > > > > > >> so
> > > > > > >> post another question here....
> > > > > > >>
> > > > > > >>
> > > > > > >> Good luck!
> > > > > > >> Sami
> > > > > > >>
> > > > > > >>
> > > > > > >>
> > > > > > >> -----Original Message-----
> > > > > > >> From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of
> > > > Claudine
> > > > > > >> Robbins
> > > > > > >> Sent: Saturday, March 19, 2005 1:15 PM
> > > > > > >> To: RBG7-L Mailing List
> > > > > > >> Subject: [RBG7-L] - RE: Access Violation Messages and
> > Sys_Layouts3
> > > > > > table
> > > > > > >> corruption
> > > > > > >>
> > > > > > >> Javier and Bernie,
> > > > > > >>
> > > > > > >> I have now reloaded the last 4 complete backup files created
> > with
> > > > > > unload
> > > > > > >> all
> > > > > > >> and every single one reports the 'data' error in RScope for
the
> > > > > > >> sys_layouts3
> > > > > > >> table.
> > > > > > >>
> > > > > > >> I also tried deleting both records in sys_layouts3 and RScope
> > > > reports
> > > > > > no
> > > > > > >> corruption. However, Access Violation messages continue and
db
> > is
> > > > > > >> unusable.
> > > > > > >>
> > > > > > >> (how do you spell seperately?) separately. And that's a good
> > idea.
> > > > > > I'm
> > > > > > >> going to do that now...
> > > > > > >>
> > > > > > >> Claudine
> > > > > > >>
> > > > > > >>
> > > > > > >>
> > > > > > >> > -----Original Message-----
> > > > > > >> > From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf
Of
> > > > Javier
> > > > > > >> > Valencia
> > > > > > >> > Sent: Saturday, March 19, 2005 1:03 PM
> > > > > > >> > To: RBG7-L Mailing List
> > > > > > >> > Subject: [RBG7-L] - RE: Access Violation Messages and
> > > > Sys_Layouts3
> > > > > > >> > table
> > > > > > >> > corruption
> > > > > > >> >
> > > > > > >> > You could try doing a RELOAD or an UNLOAD of the structure
> > and
> > > > data
> > > > > > and
> > > > > > >> > then
> > > > > > >> > reconstructing the database. TTBOMK the SYS_LAYOUTS3 table
> > stores
> > > > > > only
> > > > > > >> the
> > > > > > >> > layouts of tables of views and it is not critical, if the
> > data
> > is
> > > > > > >> deleted
> > > > > > >> > it
> > > > > > >> > should just display the tables/views in the default format.
> > > > > > >> > Javier,
> > > > > > >> >
> > > > > > >> > Javier Valencia, PE
> > > > > > >> > President
> > > > > > >> > Valencia Technology Group, L.L.C.
> > > > > > >> > 14315 S. Twilight Ln, Suite #14
> > > > > > >> > Olathe, Kansas 66062-4578
> > > > > > >> > Office (913)829-0888
> > > > > > >> > Fax (913)649-2904
> > > > > > >> > Cell (913)915-3137
> > > > > > >> > ================================================
> > > > > > >> > Attention:
> > > > > > >> > The information contained in this message and or
attachments
> > is
> > > > > > >> > intended
> > > > > > >> > only for the person or entity to which it is addressed and
> > may
> > > > > > contain
> > > > > > >> > confidential and/or privileged material. Any review,
> > > > retransmission,
> > > > > > >> > dissemination or other use of, or taking of any action in
> > > > reliance
> > > > > > >> > upon,
> > > > > > >> > this information by persons or entities other than the
> > intended
> > > > > > >> recipient
> > > > > > >> > is prohibited. If you received this in error, please
contact
> > the
> > > > > > sender
> > > > > > >> > and
> > > > > > >> > delete the material from all system and destroy all copies.
> > > > > > >> > ======================================================
> > > > > > >> >
> > > > > > >> > -----Original Message-----
> > > > > > >> > From: [email protected] [mailto:[EMAIL PROTECTED] Behalf
Of
> > > > Claudine
> > > > > > >> > Robbins
> > > > > > >> > Sent: Saturday, March 19, 2005 12:36 PM
> > > > > > >> > To: RBG7-L Mailing List
> > > > > > >> > Subject: [RBG7-L] - RE: Access Violation Messages and
> > > > Sys_Layouts3
> > > > > > >> > table
> > > > > > >> > corruption
> > > > > > >> >
> > > > > > >> > Hi Javier,
> > > > > > >> >
> > > > > > >> > I'm working with a copy. I've tried deleting both rows and
> > even
> > > > > > though
> > > > > > >> > RScope no longer reports the problem, the Access Violation
> > > > Messages
> > > > > > >> > continue...
> > > > > > >> >
> > > > > > >> > Claudine
> > > > > > >> >
> > > > > > >> > > -----Original Message-----
> > > > > > >> > > From: [email protected] [mailto:[EMAIL PROTECTED] On
Behalf
> > Of
> > > > > > Javier
> > > > > > >> > > Valencia
> > > > > > >> > > Sent: Saturday, March 19, 2005 12:33 PM
> > > > > > >> > > To: RBG7-L Mailing List
> > > > > > >> > > Subject: [RBG7-L] - RE: Access Violation Messages and
> > > > Sys_Layouts3
> > > > > > >> table
> > > > > > >> > > corruption
> > > > > > >> > >
> > > > > > >> > > Hi Claudine:
> > > > > > >> > > Whatever you do...backup first!!!
> > > > > > >> > > Then you can try deleting the offending items and see if
> > that
> > > > fixes
> > > > > > >> the
> > > > > > >> > > problem; if it does not, you still have the original...
> > > > > > >> > > Javier,
> > > > > > >> > >
> > > > > > >> > > Javier Valencia, PE
> > > > > > >> > > President
> > > > > > >> > > Valencia Technology Group, L.L.C.
> > > > > > >> > > 14315 S. Twilight Ln, Suite #14
> > > > > > >> > > Olathe, Kansas 66062-4578
> > > > > > >> > > Office (913)829-0888
> > > > > > >> > > Fax (913)649-2904
> > > > > > >> > > Cell (913)915-3137
> > > > > > >> > > ================================================
> > > > > > >> > > Attention:
> > > > > > >> > > The information contained in this message and or
> > attachments
> > is
> > > > > > >> intended
> > > > > > >> > > only for the person or entity to which it is addressed
and
> > may
> > > > > > >> > > contain
> > > > > > >> > > confidential and/or privileged material. Any review,
> > > > > > retransmission,
> > > > > > >> > > dissemination or other use of, or taking of any action in
> > > > reliance
> > > > > > >> upon,
> > > > > > >> > > this information by persons or entities other than the
> > intended
> > > > > > >> > recipient
> > > > > > >> > > is prohibited. If you received this in error, please
> > contact
> > > > the
> > > > > > >> sender
> > > > > > >> > > and
> > > > > > >> > > delete the material from all system and destroy all
copies.
> > > > > > >> > > ======================================================
> > > > > > >> > >
> > > > > > >> > > -----Original Message-----
> > > > > > >> > > From: [email protected] [mailto:[EMAIL PROTECTED] Behalf
> > Of
> > > > > > Claudine
> > > > > > >> > > Robbins
> > > > > > >> > > Sent: Saturday, March 19, 2005 12:24 PM
> > > > > > >> > > To: RBG7-L Mailing List
> > > > > > >> > > Subject: [RBG7-L] - Access Violation Messages and
> > Sys_Layouts3
> > > > > > table
> > > > > > >> > > corruption
> > > > > > >> > >
> > > > > > >> > > Hi all,
> > > > > > >> > >
> > > > > > >> > > We had a small surge at the office yesterday and one of
my
> > db's
> > > > is
> > > > > > >> > > now
> > > > > > >> > > constantly giving off access violation messages which
> > cannot
> > be
> > > > > > >> > resolved.
> > > > > > >> > >
> > > > > > >> > > Upon running RScope 7.5, I find that there is an error in
> > my
> > > > > > >> > sys_layouts3
> > > > > > >> > > table which cannot automatically be fixed because it
> > contains
> > a
> > > > > > note
> > > > > > >> > > field.
> > > > > > >> > > This table has two rows, one for an existing 'important'
> > table,
> > > > the
> > > > > > >> > other
> > > > > > >> > > for a view which I can easily delete and recreate.
> > > > > > >> > >
> > > > > > >> > > What should I do?
> > > > > > >> > >
> > > > > > >> > > Claudine
> > > > > > >
> > > > > > >
> > > > >
> > >
> > >
>
>
