I'm copying records from tables in one database to another with a
statement like this:

INSERT INTO db1.MyTable
 SELECT * FROM db2.MyTable
 WHERE ID NOT IN (SELECT ID FROM db1.MyTable)

I did this successfully with one table and was eager to move on to the
next.  But the second table gave the following error:

ElevateDB Error #601 The table MyTable is corrupt (Error reading rows
into cache)

The manual says to contact Elevate Software if this occurs during
normal operation.  I'm not quite sure what would constitute abnormal
operation, but am reporting here in case anyone has any ideas or
experience with this.

I ran this from EDB Manager 2.01 build 5 on XP-64 w/ 4 GB RAM and few
other applications running.  Both databases are on a Windows 2008
server w/ 2 GB RAM.  The table that worked successfully has 21 columns,
mostly small VARCHARs and INTs and no BLOBs.  The table that caused the
error has 43 columns, including 3 CLOBs.  I can open and browse both
tables and ran the Repair on both with no errors reported.

I will try other tables and if there are more problems, will try
writing a script.

--
David Cornelius
CorneliusConcepts.com

I tried the same thing on another table and got the following error:

ElevateDB Error #601 The temporary table OG33124432DCI_Master34 is
corrupt (Invalid BLOB block type found)


I'm going to try running this locally from the server...

--
David Cornelius
CorneliusConcepts.com

Running locally from the server wasn't any better.

So I wrote a script ...


SCRIPT
BEGIN
 DECLARE TableCursor CURSOR FOR TableStmt;
 DECLARE TableID INTEGER DEFAULT 0;
 DECLARE TableName VARCHAR DEFAULT 'MyTable';
 DECLARE Cnt INTEGER DEFAULT 0;

 USE DCI_Master;
 PREPARE TableStmt FROM 'SELECT ID FROM DCI_Mary.' + TableName +
    ' WHERE ID NOT IN (SELECT ID FROM DCI_Master.' + TableName + ')';
 OPEN TableCursor;            
 FETCH FIRST FROM TableCursor (ID) INTO TableID;
 WHILE NOT EOF(TableCursor) DO   
   SET Cnt = Cnt + 1;
   SET PROGRESS TO (CNT / ROWCOUNT(TableCursor)) * 100;

   EXECUTE IMMEDIATE 'INSERT INTO DCI_Master.' + TableName +
                     ' SELECT * FROM DCI_Mary.' + TableName +
                     ' WHERE ID = ' + CAST(TableID AS VARCHAR);
   
   FETCH NEXT FROM TableCursor (ID) INTO TableID;
 END WHILE;
 CLOSE TableCursor;
 UNPREPARE TableStmt;
END


.... but got the error 601: Table is corrupt (reading rows into cache)


Well, there goes plan A and B.  Now it's time to formulate plan C...


--
David Cornelius
CorneliusConcepts.com

Plan C was successful.  But I would've been REALLY worried if it hadn't.

I wrote a quick Delphi program to hook up the two databases and
manually copied records over, one by one via the EDBTable components.
Fortuneately, there were only three tables affected.  Still, it was a
lot of work that I wish I hadn't had to do.

Of the SQL statements that did work, it took several MINUTES to finish.
But the delphi program only took a few SECONDS.  I suppose that the SQL
needed to scan all the IDs for each pass (Plan A), but I hoped the
script (Plan B) would've avoided that.

(*sigh*)

--
David Cornelius
CorneliusConcepts.com

David,

<< Of the SQL statements that did work, it took several MINUTES to finish.
But the delphi program only took a few SECONDS.  I suppose that the SQL
needed to scan all the IDs for each pass (Plan A), but I hoped the script
(Plan B) would've avoided that. >>

First of all, please try to investigate an issue with me via email before
posting "stream of conciousness" posts to the newsgroups.  I know you're
frustrated, but these do not help in the least.

What I need from you when something like this happens is this:

1) Stop what you're doing when you first encounter the error.
2) Send me the SQL/code and the database that is experiencing the error.
3) Wait for my response.

That way we can eliminate any confusion and stick to the initial problem at
hand.

So, what I need from you is the database and tables that you were using for
the SELECT statement.

Thanks,

--
Tim Young
Elevate Software
www.elevatesoft.com

> First of all, please try to investigate an issue with me via email
> before posting "stream of conciousness" posts to the newsgroups.  I
> know you're frustrated, but these do not help in the least.

Yes, I suppose since the manual said to contact ES, I should've gone to
you directly, but you've encouraged use of the newsgroups for the
benefit of everyone.  In this case, it's probably more of an anomoly,
so I don't suppose there was any benefit to anyone else.

I was desparate for some feedback and hoped that someone reading the
newsgroup might have a suggestion.  I'm sorry for the diatribe.  I'll
follow up via email.


--
David Cornelius
CorneliusConcepts.com

David,

<< Yes, I suppose since the manual said to contact ES, I should've gone to
you directly, but you've encouraged use of the newsgroups for the benefit of
everyone.  In this case, it's probably more of an anomoly, so I don't
suppose there was any benefit to anyone else.

I was desparate for some feedback and hoped that someone reading the
newsgroup might have a suggestion.  I'm sorry for the diatribe.  I'll follow
up via email. >>

Yes, but *I'm* the one that can help you, not anyone here.  Issues like
these require some analysis, and that is my job to help you with these
things.  It simply does no good to post such a specific issue here.

--
Tim Young
Elevate Software
www.elevatesoft.com