Hi folks,

can someone see what's wrong with this:

CREATE JOB "mxgmbh_updates"
RUN AS "Administrator"
FROM DATE '2020-01-02' TO DATE '2030-12-31'
EVERY 1 MINUTES ON MON, TUE, WED, THU, FRI
BETWEEN TIME '08:00' AND TIME '17:00:00.999'
BEGIN
  DECLARE TheStoreName VARCHAR DEFAULT '';
  DECLARE TheDelFileName VARCHAR DEFAULT '';
  DECLARE procCur CURSOR FOR procStmt;
                            
  USE "mxgmbhdb";

  SET TheStoreName = 'mxgmbh';
  SET TheDelFileName = '';
  
  EXECUTE IMMEDIATE 'SET FILES STORE TO "' + TheStoreName + '"';
  PREPARE procStmt FROM 'SELECT * FROM CONFIGURATION."Files" WHERE name like ''mxgmbhupdates%'' ';
  OPEN procCur;
  IF (ROWCOUNT(procCur) > 0) THEN
     FETCH FIRST FROM procCur ('Name') INTO TheDelFileName;
     Execute Immediate 'LOAD UPDATES FOR DATABASE mxgmbhdb FROM "'+replace('.MXDBUpd' with '' in TheDelFileName)+'" IN STORE mxgmbh
     MERGE DUPLICATE INSERTS
     INSERT MISSING UPDATES';
     EXECUTE IMMEDIATE 'DELETE FILE "' + TheDelFileName + '" FROM STORE "' + TheStoreName + '"';
  END IF;
  CLOSE procCur;
END
VERSION 1.00

I see the files, can debug the script it runs for each file but the data-updates do not appear in the tables. I change data in source-db (remote system), it creates the files, transfers them to remote-store and that script should import the data but i don't see the new created entries or updated fields in destination db.
--
Yusuf Zorlu
MicrotronX

Yusuf Zorlu wrote:

> Hi folks,
>
> can someone see what's wrong with this:
>
> CREATE JOB "mxgmbh_updates"
> RUN AS "Administrator"
> FROM DATE '2020-01-02' TO DATE '2030-12-31'
> EVERY 1 MINUTES ON MON, TUE, WED, THU, FRI
> BETWEEN TIME '08:00' AND TIME '17:00:00.999'
> BEGIN
>    DECLARE TheStoreName VARCHAR DEFAULT '';
>    DECLARE TheDelFileName VARCHAR DEFAULT '';
>    DECLARE procCur CURSOR FOR procStmt;
>                              
>    USE "mxgmbhdb";
>
>    SET TheStoreName = 'mxgmbh';
>    SET TheDelFileName = '';
>    
>    EXECUTE IMMEDIATE 'SET FILES STORE TO "' + TheStoreName + '"';
>    PREPARE procStmt FROM 'SELECT * FROM CONFIGURATION."Files" WHERE
> name like ''mxgmbhupdates%'' ';    OPEN procCur;
>    IF (ROWCOUNT(procCur) > 0) THEN
>       FETCH FIRST FROM procCur ('Name') INTO TheDelFileName;
>       Execute Immediate 'LOAD UPDATES FOR DATABASE mxgmbhdb FROM
> "'+replace('.MXDBUpd' with '' in TheDelFileName)+'" IN STORE mxgmbh
> MERGE DUPLICATE INSERTS       INSERT MISSING UPDATES';
>       EXECUTE IMMEDIATE 'DELETE FILE "' + TheDelFileName + '" FROM
> STORE "' + TheStoreName + '"';    END IF;
>    CLOSE procCur;
> END
> VERSION 1.00
>
> I see the files, can debug the script it runs for each file but the
> data-updates do not appear in the tables. I change data in source-db
> (remote system), it creates the files, transfers them to remote-store
> and that script should import the data but i don't see the new
> created entries or updated fields in destination db.

I had created the db on the destination based on a backup after
activating published updates on master-server. It seems that i have to
disable the published updates on remote-server.

Now it seems to update/insert

--
--
Yusuf Zorlu | MicrotronX

Yusuf,

<< I had created the db on the destination based on a backup after activating published updates on master-server. It seems that i have to disable the published updates on remote-server. >>

Just a quick addition: there's a parameter to the RESTORE DATABASE statement to fix this called "RESET PUBLISHED TABLES":

https://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=RESTORE_DATABASE

This will ensure that the remote server doesn't end up with the same publisher IDs for the database tables being replicated.  When this occurs, the replicated updates won't load because each database thinks that it has already loaded the updates, due to the same publisher ID being in the manifest for each update.  This manifest attached to each published update is what tells EDB whether it has "seen" an update or not and, subsequently, whether it needs to load it.  This enables the ability to set up EDB in all manner of replication scenarios without worrying about duplicate updates being propagated between databases.

Tim Young
Elevate Software
www.elevatesoft.com