Use Database Automation Activities

Execute an existing SQL script, Query Builder file, or Editor file and export the results to an Excel, HTML, or CSV file.

1. Select a database connection.
2. Select a SQL file or enter a SQL statement.
   • 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 in the Editor where you can modify the SQL file.

   • Enter a SQL statement in the editor. You must first deselect the Link SQL file option.

3. 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.
4. Export File Type—Select an export file type.
5. Export File Name—Click to select a file location and enter a file name.

6. Suffix—Select a suffix to append to the file name. Select from the list of existing suffixes, or click to select a variable or build a new suffix using the expression editor.

7. Overwrite—Select to overwrite the file. If you did not add a suffix to the file name, you can deselect Overwrite to append a new worksheet to the Excel file with each script execution.
8. Advanced Properties—Click to specify advanced options.

   • Worksheet name—Enter the worksheet name or select the worksheet from the list. Overwrite must be deselected to display the list.

     To add a worksheet, select Append worksheet from the list and enter a new name.

   • Append rows—Select to append to the selected worksheet.
   • Start export at—Enter the column and row position at which to begin the export.

Tip: After exporting your file, you can use a subsequent activity in your script to email the report, to zip the report in a zip folder, or to copy the report to another location.

Execute an existing SQL script, Query Builder file, or Editor file. You can save the results of the script in a variable.

1. Select a database connection.
2. Select a SQL file or enter a SQL statement.
   • 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 in the Editor where you can modify the SQL file.
   • Enter a SQL statement in the editor. You must first deselect the Link SQL file option.

3. Save result set in variable—Enter a name for the result variable, which contains a data table with the results of the execution script.

   Tip: You can use the result variable with the Loop Dataset activity. See Use System Automation Activities for more information.

Review the following for additional information:

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

Execute an existing SQL script or Query Builder query and save it and any results sets in a Toad editor file (.tef).

This file format is useful for building scripts that have large result sets that you do not want to continue executing or for saving results when you have not finished building a script.

1. Select a database connection.
2. Select a SQL file or enter a SQL statement.
   • 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 in the Editor where you can modify the SQL file.
   • Enter a SQL statement in the editor. You must first deselect the Link SQL file option.

3. Result saved in Editor file—You can select an Editor file from an open project or browse to select a file. Click to edit the selected file.

   Tip: You can use a variable in this field by entering the hash symbol before and after the variable name (#VariableName#).

4. Append results—Select to include the result set in the Editor file.

Review the following for additional information:

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

• Suffix—Select a suffix to append to the file name. Select from the list of existing suffixes, or click to select a variable or build a new suffix using the expression editor.

Export data from databases, tables, views, or a query into a CSV, Excel, or other file.

Review the following for additional information:

• Export template—You can select an existing template, edit an existing template, or create a new template.

  Tip: You can use a variable in the file name to select an existing template file by entering the hash symbol before and after the variable name (#VariableName#).

• Use Automation Connection—Select this option to use the connection associated with the Export Wizard activity. Clear the checkbox to use the connection associated with the export template.

Note: See Export Data with the Export Wizard for more information.

Import data from a CSV, Excel, or other file into a new table or append to an existing table.

Review the following for additional information:

• Import template—You can select an existing template, edit an existing template, or create a new template.

  Tip: You can use a variable in the file name to select an existing template file by entering the hash symbol before and after the variable name (#VariableName#).

• Use Automation Connection—Select this option to use the connection associated with the Import Wizard activity. Clear the checkbox to use the connection associated with the import template.

Note: See Import Data for more information.

Use this activity to open a Toad Data Report (executing the query) and then export the results to a file using the layout specified in the Toad Data Report. Several export file formats are available, including PDF, HTML, and Excel.

You can also select multiple Toad Data Reports and export the results to a single file.

Note: You must create a Toad Data Report file (.tdr) before you can select it using this activity.

Review the following for additional information:

• Select a connection to execute the Toad Data Report query against.
• Add Reports—Click to browse to and select one or more Toad Data Report files (.tdr). Click the arrow and select From Project Manager to add the reports in the currently-open project.
• Edit Report—Click to edit the selected report.
• 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.
• Export file—Enter a name and select a file type and location for your export file. You can use a variable in this field by entering the hash symbol before and after the variable name (#VariableName#).

• Suffix—Select a suffix to append to the file name. Select from the list of existing suffixes, or click to select a variable or build a new suffix using the expression editor.

For more information about Toad Data Reports:

• See About Designing Toad Data Reports to learn how to create a Toad Data Report.
• See Automate Toad Data Reports (tutorial).

Refresh an existing Toad Pivot Grid file and export results to an Excel, HTML, or CSV file. You can then add another activity to your script to zip the new file, attach it to an email, or copy it to a new location.

Note: You must create a Toad Pivot Grid file (.tpg) before you can select it using this activity.

Review the following for additional information:

• Connection—Select a connection to execute the Toad Pivot Grid file against.
• Pivot file—You can select a Toad Pivot Grid file from the currently-open project or browse to select a file. Click to edit the selected pivot file.
• Export file—Enter a name and select a file type and location for your export file. You can use a variable in this field by entering the hash symbol before and after the variable name (#VariableName#).

• Suffix—Select a suffix to append to the file name. Select from the list of existing suffixes, or click to select a variable or build a new suffix using the expression editor.

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

Open a Toad Data Compare project (.dcp) and export the contents as a summary report and detailed object reports. Select from a number of output file formats for the summary report, including PDF and Excel.

• Data Compare Project—Select an existing Toad Data Compare project file.
• Summary Report—Select a file location, file name, and file type for the comparison summary report file.
• Object reports folder—Select a destination folder for the detailed object comparison reports generated by the activity.
• Generate sync script—Select a location and enter a file name to generate a synchronization script. Click to configure options for the synchronization script.
• Execute script—Select to execute the generated script.
• Suffix—Select a suffix to append to the file name. Select from the list of existing suffixes, or click to select a variable or build a new suffix using the expression editor.

Opens a schema compare project and save reports.

Review the following for additional information:

• Schema compare project—Select a Toad schema compare project file and configure it if necessary.
• Summary report, Detail report—Select a file location, file name, and file type for the report files.
• Synchronization script—Select a file location and file name. Check Execute script to run it as part of a automation activity.

• Suffix—Select a suffix to append to the file name. Select from the list of existing suffixes, or click to select a variable or build a new suffix using the expression editor.

In previous versions of Toad, the Database Connection activity was required in order to specify database connection details.

In the current version of Toad, you can specify the database connection details within each applicable activity, eliminating the need to use the Database activity. You may continue to use the Database activity if you prefer.

Note: The database connection specified for the Database Connection activity takes precedence over the connection specified for the individual activity.

Loops through multiple connections and executes the activities you specify. Add as many activities as you want to the loop. The Loop Connections activity can have more than one branch.

• Select connections to loop through—Click Add Connections to select one or more connections to loop through.

• Connection description variable—Enter a new name for the variable or use the default name. The connection description variable is a SQL-type variable comprised of one row containing the connection information for the current connection.

  Use the following column names to gather connection information from the connection description variable. The exact content of each column depends on the provider type.

  Column Name	Description
  DescriptionLong	Description of the connection details.
  ProviderType	Provider type such as SQLServer, Oracle, or Sybase.
  HostName	Hostname or instance for the connection.
  UserName	User name.
  TrueUserName	If the connection supports and uses Windows authentication (e.g., SQL Server connection) this column contains the current Windows Logon name as: domain\username (e.g., "PROD\bgates"). Otherwise, if database authentication is used, it contains the same as the UserName column.
  Database	Default Database context for the connection.

• Next, add activities to the loop to execute for each connection in the connection list.

Note: The connections you specify in the Loop Connections activity take precedence over any connection specified in an individual activity.

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

• For each applicable activity, you can specify the database connection in the activity's properties. The default connection is the currently-active open connection.