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.
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”.