Automate Tasks
Use Automation to schedule a single script to run reports and queries, save results in Excel spreadsheets, and distribute results to the business community via email. You can schedule scripts that perform long-running tasks to run when database activity is light.
You can also use variables in automation scripts. With variables, you can update information that may be different each time you run the script, such as the department name, revenue, fiscal quarter, or product name. See Using Variables in Automation for more information.
For example, assume you are an analyst at Nick's Flicks movie rentals. You need to run inventory and movie rental reports on a daily basis and distribute them to managers of the inventory department. Using Toad, you can create an automation script that runs the reports, exports them to Excel spreadsheets, and emails the spreadsheets to the inventory department everyday at 2:00 AM.
Troubleshooting: You must surround a variable name with the # sign (for example, #sleep#).
Notes:
- Automation scripts are compatible with the version of Toad in which they are created.
- The Automation module is disabled if Toad was installed with the Prohibit saving passwords option selected.
To automate tasks
- Select Tools | Automation.
-
Click Settings (icon) in the script design window and specify script settings. Enter settings in the bottom pane. Review the following for additional information:
Activity Input Tab Stop or continue on Error
Select Stop On Error to stop the script if an error occurs. Send email when error occurs
Select this option to send an email on script error.
If selected, you must also click Compose Email to compose the email message and to edit the Email Server Settings.
Note: When you specify the error email settings, Toad saves your settings and uses them in all subsequent automation scripts.
Use relative path to find files
Select to convert all local file paths to relative paths in all activities in the script (network file paths remain unchanged). This is useful if you want to share your automation scripts with colleagues or place them on a shared network.
Toad makes the change the next time you run or save the script.
If you deselect this option later, the script reverts back to the original absolute paths.
Embed files into Automation script
Embeds Toad input files into the script. This makes it easier to share the script with colleagues.
Important: To use this option, deselect it, build your script, select input files, then re-select this option. This allows Toad to collect and save the input file paths.
This option embeds Data Compare, Pivot Grid, Toad Data Report, and Import/Export template files. Scripts used by the Run Automation Script activity are also embedded, as are SQL query files used as input by an Import/Export template.
Important: Files linked using the Link to File option in the Select to File, Execute Script, and Select to Editor with Results activities are not embedded [.sql, Editor (.tef), and Query Builder (.tsm) files)].
Toad embeds the files the next time you run or save the script.
If necessary, at a later time you can deselect this option and then re-establish links to the original files.
Truncate log
Select to overwrite the log file each time the script runs. Selected environment
Use this area to specify a "test" database connection and a "production" database connection and then easily switch between the two.
- Test—Select to run your script in test mode using the database connection and other settings you specified under Test environment.
- Production—Select to run your script in production mode using the database connection and other settings you specified under Production environment.
Note: The Connection field under both environments defaults to the currently-active open connection.
Variable names
- Root path—Enter a variable name or use the default name. Then use this variable name in your script wherever you want to apply the path defined under Root path in either the Test settings or the Production settings. The path that is applied depends on which environment (Test or Production) is selected at the time the script is executed.
- Run mode—Enter a variable name or use the default name. The value assigned to this variable is either Test or Production, based on the environment selected at the time the script is executed.
Activity Info Tab Name
Enter a script name. -
To build a script, single-click an activity in the Toolbox, or drag an activity from the Toolbox to the Automation script design window. Repeat this process to add additional activities to your script.
Note: You must drag the activity to
, then release the mouse button after you see the activity's icon replace
-
Click an activity in your script workflow in the design window and then specify the activity's properties in the details pane (bottom pane). Repeat this process for each activity in your script. For a step by step tutorial on building an Automation script, see Getting Started with Automation.
See the following topics for details about each activity and its properties:
Tip: You can save an activity and its settings as a template to reuse in other scripts. Right-click the activity in the design window and select Save As Template. After you create the first template, a Templates toolbox displays.
Note: Click Settings in the design window at any time to go back and review the Automation script settings.
- When you finish building your script, select one of the following actions from the Automation toolbar:
Add an automation script to the current project in the Project Manager. The script is automatically placed in the project's Automation Scripts folder.
Save an automation script (.tas) in a folder on a local or network drive.
Run an automation script before scheduling it. The script executes once and the execution log is displayed in the Log window. Resolve any problems that cause activities to fail, or disable failed activities before scheduling the script.
Tips:
- Press F5 to run the automation script.
- To view the script log, select the Log tab or select View | Output.
Schedule an automation script in the Job Manager. You can schedule a script to execute once or to run periodically at a defined interval. See Execute and Schedule Automation Scripts for more information.
Important: To rename an Automation script file, do so through Toad (File | Save File As), not through Windows Explorer. Renaming a script file using Toad ensures that log files are created correctly and properly synced with their scripts.
Tips:
- Double-click an activity in the script design window to open the Activity Properties pane in a format used in earlier versions of Toad.
- Right-click within the script design window and select Print Script to print the script's workflow diagram.