Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 5 of 5 total |
Job to delete some tables |
Fri, Sep 14 2018 12:26 PM | Permanent Link |
Jose Eduardo Helminsky HPro Informatica | 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> |
Fri, Sep 14 2018 1:59 PM | Permanent Link |
Fernando Dias Team Elevate | 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] |
Fri, Sep 14 2018 3:34 PM | Permanent Link |
Jose Eduardo Helminsky HPro Informatica | 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> |
Fri, Sep 14 2018 3:46 PM | Permanent Link |
Jose Eduardo Helminsky HPro Informatica | 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 |
Fri, Sep 14 2018 6:28 PM | Permanent Link |
Fernando Dias Team Elevate | 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] |
This web page was last updated on Tuesday, September 17, 2024 at 04:19 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |