Andrew

I can take some guesses but it would help to know what errors you get. I'll have a play with your code when I have a few minutes.

My first guesses are:

1. You're writing a script into a query. I'm not sure what the differences are under the hood but it may cause problems.
2. The query is run under the configuration database so the table may not be recognised


Just a general observation. You're going to be creating this script every time you want to run this task. You're trying to create a script on the fly which will work through all the tables. So either write the script and test it in EDBManager, make it a Stored Procedure and you can then just call it or, since you're using Delphi & EXECUTE IMMEDIATE anyway why not just use it a bit more. Get a list of tables (use LIST and stuff the result into a stringlist), loop through the scringlist and use the EDBDatabase.Execute method - one call with REPAIR followed by one for OPTIMISE.


Roy Lambert

Andrew


Part of the problem is you've tried to be to clever.I'd suggest that you drop a TEDBScript coponent on the form, set the SQL  to


SCRIPT
BEGIN

DECLARE TBLCrsr CURSOR FOR TBLStmt;
DECLARE TBLName VARCHAR(40);

PREPARE TBLStmt FROM ' SELECT Name FROM Information.Tables ';
OPEN TBLCrsr;
FETCH FIRST FROM TBLCrsr ('Name') INTO TBLName;
WHILE NOT EOF(TBLCrsr) DO
 EXECUTE IMMEDIATE 'REPAIR TABLE '+TBLName;
 EXECUTE IMMEDIATE 'OPTIMIZE TABLE '+TBLName;
 FETCH NEXT FROM TBLCrsr ('Name') INTO TBLName;
END WHILE;

END

and the DatabaseName to whatever the database yoou're using is.

Then all you need to do is call the ExecScript method. You can wrap your try except handling round that and preface with trying to get exclusive access.

if you don't want to drop a TEDBScript component on the form then you can create one dynamically and assign the sql above to it. Same thing about setting the DatabaseName. I'd recommend just doubling up the quotes rather than putting in the CHR call.


Roy Lambert

Roy, that did the job - thanks