Since no-one seems to be answering, I'll guess...

I assume that your error is not due to some kind of error in your database file that you've simply copied from one server to another. I also assume you've checked that the table structure of all six tables are 'pair-wise' identical.

Do all tables have the same character set and are you using that character set also when you call EXECUTE PROCEDURE? Have you checked whether you can manually read and delete the record(s) that has the two allegedly offending values(588217574 and
17-NOV-2009 13:05:51.0000)?

Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of piotr_3m
Sent: 6. mai 2010 09:33
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Move data problem

Hi, I have a problem with SP. It moves data from one table to another of the same structure. I have over 1000 DB system and on most it works fine, but on some it fails in a weird way. I have been looking for an answer everywhere before I posted here.

Hint - it fails moslty when DB is on linux. My app is on Windows - I also get this problem with FlameRobin. My databases are 2-10GB in size.

My SP:
SET TERM ^ ;
ALTER PROCEDURE MOVE_RECORDS_TO_ARCHIVE (
INID_REJ Integer,
INFIRSTGAP Bigint )
RETURNS (
OUTRECORDCOUNT Integer )
AS
declare variable locDetails_wskhead bigint;
begin
outRecordCount = 0;
for select max(wskhead) FROM DETAILS where details.ID_REJ__DETAILS=:inID_Rej and details.wsktail<:inFirstGap into
:locDetails_wskhead /* conajmniej jeden wpis w dane ktorego detailsy zostaly przeniesione do details_archive musi zostac w tabeli dane bo bedzie problem z indexami jak sie od nowa stworzy*/
do
begin
INSERT INTO DANE_ARCHIVE
SELECT * FROM DANE where DANE.ID_REJ__DANE=:inID_Rej and DANE.wsktail<:locDetails_wskhead order by wskhead;
delete FROM DANE where DANE.ID_REJ__DANE=:inID_Rej and DANE.wsktail<:locDetails_wskhead;
INSERT INTO ZRODLA_ARCHIVE
SELECT * FROM ZRODLA where ZRODLA.ID_REJ__ZRODLA=:inID_Rej and ZRODLA.wsktail<:inFirstGap order by wskhead;
delete FROM ZRODLA where ZRODLA.ID_REJ__ZRODLA=:inID_Rej and ZRODLA.wsktail<:inFirstGap;
INSERT INTO DETAILS_ARCHIVE
SELECT * FROM DETAILS where details.ID_REJ__DETAILS=:inID_Rej and details.wsktail<:inFirstGap order by wskhead;
delete FROM DETAILS where details.ID_REJ__DETAILS=:inID_Rej and details.wsktail<:inFirstGap;
end
end^
SET TERM ; ^


Errors:
Overflow occurred during data type conversion.Conversion error from string "588217574".
At procedure 'MOVE_RECORDS_TO_ARCHIVE' line: 19, col: 5.

Overflow occurred during data type conversion.Conversion error from string "17-NOV-2009 13:05:51.0000".
At procedure 'MOVE_RECORDS_TO_ARCHIVE' line: 19, col: 5.


The weird - line 19 (the error) is:
delete FROM ZRODLA where ZRODLA.ID_REJ__ZRODLA=:inID_Rej and ZRODLA.wsktail<:inFirstGap;

If I remove this line from SP it works!