Toad for SQL Server 7.4.1 - Installation Guide

Automation variables are a powerful tool that can greatly extend your scripts by adding flexibility and customization. You can use variables to represent and store data that may be different each time the script executes. Variables can be used with almost every Automation activity.

Use this activity to define one or more variables.

Review the following for additional information:

• Variable name—Enter a name for the variable. When you use the variable name later in the script, for example in a file name or in an expression, surround the variable name with the hash symbol (#MyVariable#).
• Variable type—Specify a variable type.
  • SQL—Select SQL if you plan to use the result set of a query as the variable value.
  • DateTime—See Using Variables in Automation for more information about date variables.
• Variable value—Enter a value or click to build an expression.
  • If you click to build an expression, select from the list of variables, operators, and functions. Click a category in the left pane to display a list of items in the right pane. Double-click an item to add it. Click Test to test the expression.

  • If you selected SQL as the variable type, the variable value options expand to allow you to specify a query. Select a database connection, then select a SQL file or enter a SQL statement. Toad uses the result set of the query as the value for your variable.

    See Using Variables in Automation for more information about SQL type variables.

• Prompt during run—Select this option to instruct Toad to prompt for a new variable value during manual execution. The new value is used for the current execution only. The default value specified in Variable value remains unchanged. The default value is used for a scheduled script.

• Add—Click to specify an additional variable/value pair. Use Up and Down buttons to reorder list.

To learn more about variables, see the following:

• Using Variables in Automation
• Using Date Formats in Automation
• Automation and Variables (tutorial)

Use this activity to assign a new value to a variable previously defined in the current script or current activity. You can assign a new value to any variable type, except SQL.

Review the following for additional information:

• Variable name—Select a previously-defined variable.
• Variable type—Displays the variable type of the selected variable.
• Variable value—Enter a new value or click to build an expression. For more information, see Variable Value in the Set Variable section above.
• Add—Click to select an additional previously-defined variable.

See Variable Scope in Using Variables in Automation to learn more about how to use this activity with the Set Variable activity.

Execute one or more activities if a condition you specify evaluates to true. You can add several branches to the If Condition activity to simultaneously evaluate more than one condition.

To learn how to use the If Condition activity, see the following tutorial:

• Using If Condition Activity (tutorial)

Use the expression editor to build an expression that evaluates a condition. Typically this includes a variable (user-defined or built-in).

• Expression—Build your expression by selecting from the list of variables, operators, and functions. Click a category in the left pane to display a list of items in the right pane. Double-click an item to add it. Click Test to test the expression.

This activity can have one or more branches. To delete a branch, select it and press Delete. To add a branch, right-click the activity (the outside loop) and select Add Branch. If you have multiple conditions, they do not have to be mutually exclusive. Multiple branches can be true.

To learn more about variables, see the following:

• Using Variables in Automation
• Automation and Variables (tutorial)

Loops through each row in a dataset and performs the activities you specify. The dataset is the result set of a query you provide (the driving query) and is stored in the dataset variable. Add as many activities as you want to the loop. The Loop Dataset activity can have more than one branch.

Tip: You also have the option to select any SQL-type variable created in a previous activity to use as the dataset variable. Activities that create SQL-type variables include Execute Script, Set Variable, or Loop Dataset.

To learn how to use the Loop Dataset activity, see the following tutorial:

• Using Loop Dataset Activity (tutorial)

Review the following for additional information:

• Dataset variable—Enter a new name for the dataset variable or use the default name.
• Select dataset variable—(Optional) You can select one of the SQL-type variables created in a previous activity to use as the dataset variable.
• Connection—Select a connection.
• SQL script file—Select a SQL file to use as the driving query. After the script executes, the result set of the driving query is stored in the dataset variable.
  • Click the drop-down list to select a file from the currently open project in the Project Manager.
  • Click to browse to and select a SQL file.
  • Click to open the SQL file (you just selected) in the Editor where you can modify the SQL.

  Note: Click to preview the query results.

• Link SQL file—Select to link the SQL script file you selected in the previous step.
• SQL script—Enter a SQL statement to use as the driving query (if you did not select a SQL file).
• RowCount variable—Enter a name for the row count variable or use the default. The value assigned to this variable equals the number of rows returned from the activity. See Using Variables in Automation for more information.

Tip: In the Loop Dataset, you can use the following format to filter data from the dataset variable when referencing it: <dataset variable name>.<filter parameter>.

Example: Loop_data_1_SQL.region_id, where region_id is a column name

See Using Loop Dataset Activity for an example of how to use this format.

The While activity runs a loop while a condition you specify is true. The activity ends when the condition becomes false, or when the activity completes the selected number of loops. Each loop executes the activities you specify.

To learn how to use the While activity, see the following tutorial:

• Using the While Activity (tutorial)

Use the expression editor to build an expression that evaluates a condition. Typically this includes a variable (user-defined or built-in).

• Expression—Build your expression by selecting from the list of variables, operators, and functions. Click a category in the left pane to display a list of items in the right pane. Double-click an item to add it. Click Test to test the expression.

• Loop—Enter the number of times to loop the activity. If the condition never becomes false, Toad ends the activity after this number of loops. The default is 10.

This activity can have one or more branches. To delete a branch, select it and press Delete. To add a branch, right-click the activity (the outside loop) and select Add Branch. If you have multiple conditions, they do not have to be mutually exclusive. Multiple branches can be true.

To learn more about variables, see the following:

• Using Variables in Automation
• Automation and Variables (tutorial)

Use this activity to distribute reports produced by previous activities in the script or to send notification of script execution status.

Note: To use this activity, an SMTP service must be running.

Review the following for additional information:

• From—Enter your email address or click to auto-populate this field with your email address.

  Note: If you encounter a security alert message from your email application, and you want to use this auto-populate feature, grant Toad access (at least temporarily).

• To—Enter an email address or click to auto-populate this field with your email address.

  • Prompt—Select to convert the To field to a variable and instruct Toad to prompt for a new variable value during manual execution. The new value is used for the current execution only. The default value specified in To remains unchanged. The default value is used for a scheduled script.

• Email Server Settings—Click this button to specify your SMTP email server settings.

  • Server—Enter the address of your SMTP email server or click to auto-populate this field. If Toad is unable to auto-populate this field, contact your email administrator for the SMTP email server address.

    Note: The email server setting automatically defaults to that specified in the script's Settings (click the Settings icon and then click the Compose Email button).

  • User name / Password—If a non-SSL connection requires authentication, enter credentials. If using a secure connection, enter credentials, select Use Secure connection and select SSL or TLS.
• Subject, Body—Enter a subject line and enter text for the body of your email.
• Add Attachments—Click to add files created by this script or to browse for other files to add as attachments. In the File Collection Editor do one or both of the following:
  • Select one or more previously created files in the left pane and click Add.

  • Click and browse to select an existing file.

    In this field, you can also use the asterisk (*) and question mark (?) wildcard characters, as well as variables, in the file name to help you select one or more files (in the same directory) to add. To use this method:

    1. Specify the file directory by browsing to and selecting one of the files.
    2. Then, in the File Collection Editor, modify the selected file name (in the right pane) using wildcard characters and/or variables.

• Use file for body—Select a file to use as the body of the email. This is useful if you want to include report contents in the body of the email instead of as an attached file. For example, in the Select to File activity you can select HTML as the output file format, and then use this HTML file as the body of the email.

  In another scenario, you can use a form letter as the body of your email. Create a form letter template and then customize the file with the Find and Replace activity. See Automate Find and Replace for more information.

Note: In the To, CC, Subject, and Body fields: You can use a variable in this field by entering the hash symbol before and after the variable name (#VariableName#).

To learn more about how to use email in an Automation script, see the following:

• Automate Sending Email (tutorial)

Run a program with optional command-line arguments. Review the following for additional information:

• Program—Select a batch file (.bat) or executable (.exe) file.

• Arguments—Enter command-line arguments. You can also enter a variable, such as #sleep# in this field.

  Note: If you want to run a macro in Access, enter databasename /x macroname in this field. For example, if you have a macro that beeps and runs in your Northwind database, you would enter Northwind 2007.accdb /x beep.

• Run directory—Select the directory to change to, when running the program.
• Run style—Select the window state for the program when it runs.
• Verb—Enter verbs used when running the program, such as "Print".
• Wait for program to exit—Select this option to pause if there is an error. Select duration of wait time in minutes.

• Return code variable—Enter a variable name or use the default name. You can use this variable with the If Condition activity.

  Return code (exit code) values of 16384 (4000 hex) or larger do not generate a script error in this activity. Other return code values do. This allows you to use your program to generate a custom return code value in this range and use it in subsequent activities without generating a script error.

Note: If the script opens a command-line window, you must manually close the window once the script completes.

Run another automation script in your current automation script.

Review the following for additional information:

• Automation script—Select an automation script (.tas file).
  • After selecting a script, click to open the child script in a new window.
• Return code variable—Enter a variable name or use the default name. The return code variable is a built-in variable. After the child script executes, the return code value is stored in this variable. Return code values are:
  • 0 = Successful execution
  • 999 = An error occurred during execution
  • 111 = An error occurred, but you selected continue on error.

  You can use this variable with the If Condition activity.

Stops an activity for a specified number of seconds before continuing. This is useful, for example, if you are using parallel activities and need to pause one branch while waiting for results or the complete execution of another branch.

For an example of how to use the Pause activity in an Automation script, see the following:

• Using the While Activity (tutorial)

Use this activity to generate an error and add a custom error message to the script execution log.

Select Stop On Error to stop the script after the error is logged. This option is independent of the global Stop/Continue on Error option specified in script Settings or the Stop on Error option in the Activity Info tab. This activity generates a script exit code of 555.

Run two or more activities in parallel.

After adding the activity to the design window, hover over the activity to display a drop-down list of view options.

View Parallel—Normal view.

View Cancel Handler—Select to add cancel handler activity.

View Fault Handlers—Select to add fault handler activity.

This activity can have one or more branches. To delete a branch, select it and press Delete. To add a branch, right-click the activity (the outside loop) and select Add Branch.

Notes: 

• To disable an activity, right-click the activity in the design window and select Disabled. This is helpful if you want to disable an activity while testing other activities.
• The Stop on Error option for each activity allows you to select whether to stop or continue execution if Toad encounters an error during that activity.
• To prevent an error in one activity from causing a failure at the script level, clear the Generate Error check box (Activity Info tab) for that activity. See Automation Script Troubleshooting and Error-Handling.
• Select the Log tab to review a script's execution log.
• In the Suffix field, the drop-down list includes two predefined suffixes (Date and DateTime). The list also includes any suffixes you have created and saved since installing Toad. To delete a suffix from the list, select the suffix, click , delete the suffix in the Expression field, and click OK.
• Automation does not support positional parameters denoted by a question mark.