Toad World® Forums

Automation export to Access fails in Scheduler

I am trying to schedule an automation script and am running into an “invalid object name” error each time it runs on a schedule. I run a SQL script and then have export templates to export temp tables to an Access DB. Everything works fine when I run the auto script manually but when I let it run on its job without having a Toad instance open in the foreground, the SQL will run and then the first export template will fail with the invalid object name. I have not had any issues with my execution script, only the export templates not picking up the tables after the SQL runs during a scheduled job. It’s as if the connection is dropped between the two steps and the temp tables are deleted, even though I have all of the execution and export steps in the connection “bubble.” I have had the same issue whether I check Use Automation Connection or not.

Has anyone run into this?

Thanks!

Hello AM123,

Please note that when you execute a SQL statement manually in the editor window you are able to select the “current” database from the drop down list on top of the editor. That means if you create a table it will be stored inside this database/schema. However in automation you have to either specify the default database inside connection properties or explicitly write database name before the tables… i.e. “select * from table1” replace with “select * from myDb.dbo.table1”.

If that doe not help, please do the following:

  1. Enable Verbose logging (Automation script > Settings activity > Logging level)
  2. Send me the automation script (.tas file)
  3. Send me the log file for this automation script (i believe it’s in the same folder as the script)
  4. Please clarify what DB provider are you using in the first step (Execute sql)?
    For attachments please send it to martin.holkovic@quest.com

I will try to simulate at my side,

Martin