Hi everyone

I am new to ElevateDB (comming from DBISAM) and I am discovering the power of JOBS.

The idea is to drop some tables according their names. I am testing with the code below but it is not workink. I saw the logs and the job is executing every minute but the tables are still there. There is no users using the database.

<CODE>
CREATE JOB TmpTables
RUN AS "System"
FROM DATE '2018-01-01' TO DATE '2100-12-31'
EVERY 1 MINUTES
BETWEEN TIME '01:00 AM' AND TIME '11:30 PM'
BEGIN
    DECLARE xCursor CURSOR FOR xSql;
    DECLARE xTableName VARCHAR DEFAULT '';
    PREPARE xSql FROM 'select * from information.tables where name like ''TMP_%''';
    OPEN xCursor;
    FETCH FIRST FROM xCursor('Name') INTO xTableName;
    WHILE NOT EOF(xCursor) DO
       EXECUTE IMMEDIATE 'drop table "' + xTableName + '"';
       FETCH NEXT FROM xCursor('Name') INTO xTableName;
    END WHILE;
    CLOSE xCursor;
END
</CODE>

Eduardo,

Inside a Job the default database is "Configuration".
You have to change the default database with "USE <DatabaseName>" or prefix all table names with the database name.
In what database are you wanting to delete those temp tables?

--
Fernando Dias
[Team Elevate]

Fernando

The name of database is "DB".

I have already tried the following script but it does not work but I realize the log does not fired by it. In the old one, without database (use "DB") the log was populated with the run every minute altought it does not delete the tables.

<CODE>
CREATE JOB TmpTables
RUN AS "System"
FROM DATE '2018-01-01' TO DATE '2100-12-31'
EVERY 1 MINUTES ON MON, TUE, WED, THU, FRI, SAT, SUN
BETWEEN TIME '05:00 AM' AND TIME '05:30 AM'
CATEGORY 'TmpTables'
BEGIN        
    DECLARE xCursor CURSOR FOR xSql;
    DECLARE xTableName VARCHAR DEFAULT '';
    USE "DB";     
    PREPARE xSql FROM 'select * from information.tables where name like ''tmp_%''';    
    OPEN xCursor;
    FETCH FIRST FROM xCursor('Name') INTO xTableName;
    WHILE NOT EOF(xCursor) DO
       EXECUTE IMMEDIATE 'drop table "' + xTableName + '"';
       FETCH NEXT FROM xCursor('Name') INTO xTableName;
    END WHILE;
    CLOSE xCursor;
END
</CODE>

Fernando

Sorry my last post.

The famous "CTRL+C/CTRL+V" and I did not realize the time has changed. After I replace the interval with proper values it works like expected.

Thank you very much.

Eduardo

Eduardo,

Great, I'm glad to hear it worked - it has to
I have been using JOBs to backup databases and exchange data between servers for a few years now and it never failed.

--
Fernando Dias
[Team Elevate]