Learn more about Toad for SQL Server
at Quest.com

Find solutions and downloads at the
Toad for SQL Server Support Portal

< To bookmark a page, right-click the Page Title and select Add to Favorites / Bookmark This Page

Toad for SQL Server 5.7

You are here: Automate Tasks > Automate Tasks

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 Use 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 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:

If Toad displays a "Variable must be initialized before referencing itself" error, you need to initialize the variable. See Use System Automation Activities for more information.
You must surround a variable name with the # sign (for example, #sleep#).

Note: This topic focuses on information that may be unfamiliar to you. It does not include all step and field descriptions.

To automate tasks

Select Tools | Automation.

Select the Settings icon in the script design window to specify the script settings before building your script. Use the Activity details pane in the bottom portion of the Automation window to enter your settings. Review the following for additional information:

Activity Input
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 SQL files	Select to use a relative path that does not include the drive or full path for all activities in the script. 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 folder.
Embed SQL into Automation script	Select to embed the actual SQL from SQL files in the script. This makes it easier to share the script with colleagues.
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
Name	Enter a script name.

To build your script, click an activity in the Toolbox, or drag an activity from the Toolbox to the Automation script design window. Repeat to add additional activities to your script.

Note: You must drag the activity to the , then release the mouse button after you see the activity's icon replace the .
For each activity in your script, select the activity in the design window and then specify the activity's properties in the Activity details pane.

See the following topics for details about each activity and its properties:
Tip: You can save an activity and its properties 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 Automation Scripts for more information.

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.