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#).

Note: Automation scripts are compatible with the version of Toad in which they are created. To learn how to upgrade your scripts and scheduled tasks that were created in a previous version of Toad, see Upgrade Automation Scripts and Scheduling Tasks.

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, use them with version control, 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.
Run Mode	Select Test mode to run your script in test mode using the database connection specified under Test settings. Select Production mode to run your script in production mode using the database connection specified under Production settings. Note: The default connection is the currently active, open connection.
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.