Toad® Data Point 6.

User Guide
 Copyright

© 2025 Quest Software Inc.

ALL RIGHTS RESERVED.
This guide contains proprietary information protected by copyright. The software described in this guide is furnished
under a software license or nondisclosure agreement. This software may be used or copied only in accordance with
the terms of the applicable agreement. No part of this guide may be reproduced or transmitted in any form or by any
means, electronic or mechanical, including photocopying and recording for any purpose other than the purchaser’s
personal use without the written permission of Quest Software Inc.
The information in this document is provided in connection with Quest Software products. No license, express or
implied, by estoppel or otherwise, to any intellectual property right is granted by this document or in connection with
the sale of Quest Software products. EXCEPT AS SET FORTH IN THE TERMS AND CONDITIONS AS
SPECIFIED IN THE LICENSE AGREEMENT FOR THIS PRODUCT, QUEST SOFTWARE ASSUMES NO
LIABILITY WHATSOEVER AND DISCLAIMS ANY EXPRESS, IMPLIED OR STATUTORY WARRANTY
RELATING TO ITS PRODUCTS INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTY OF
MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE, OR NON-INFRINGEMENT. IN NO EVENT
SHALL QUEST SOFTWARE BE LIABLE FOR ANY DIRECT, INDIRECT, CONSEQUENTIAL, PUNITIVE,
SPECIAL OR INCIDENTAL DAMAGES (INCLUDING, WITHOUT LIMITATION, DAMAGES FOR LOSS OF
PROFITS, BUSINESS INTERRUPTION OR LOSS OF INFORMATION) ARISING OUT OF THE USE OR
INABILITY TO USE THIS DOCUMENT, EVEN IF QUEST SOFTWARE HAS BEEN ADVISED OF THE
POSSIBILITY OF SUCH DAMAGES. Quest Software makes no representations or warranties with respect to the
accuracy or completeness of the contents of this document and reserves the right to make changes to
specifications and product descriptions at any time without notice. Quest Software does not make any commitment
to update the information contained in this document.
If you have any questions regarding your potential use of this material, contact:
Quest Software Inc.
Attn: LEGAL Dept
4 Polaris Way
Aliso Viejo, CA 92656
Refer to our web site (www.quest.com) for regional and international office information.
Patents
Quest Software is proud of our advanced technology. Patents and pending patents may apply to this product. For
the most current information about applicable patents for this product, please visit our website at
www.quest.com/legal.
This product includes patent pending technology. For the most current information about applicable patents for this
product, please visit our website at www.quest.com/legal.
Trademarks
Quest, Quest Software, Benchmark Factory, Spotlight, LiteSpeed, SQL Navigator, vWorkspace, Toad, and the
Quest logo are trademarks of Quest Software Inc. in the U.S.A. and other countries. For a complete list of Quest
Software trademarks, please visit our website at www.quest.com/legal. Microsoft, Windows, Windows Server,
Visual Studio, SQL Server, SharePoint, Access and Excel are either registered trademarks or trademarks of
Microsoft Corporation in the United States and/or other countries. Oracle is a trademark or registered trademark
of Oracle and/or its affiliates in the United States and other countries. Citrix® and XenApp™ are trademarks of
Citrix Systems, Inc. and/or one or more of its subsidiaries, and may be registered in the United States Patent
and Trademark Office and in other countries. SAP is the registered trademark of SAP AG in Germany and in
several other countries. IBM and DB2 are registered trademarks of International Business Machines
Corporation. MySQL is a registered trademark of MySQL AB in the United States, the European Union and
other countries. Google and Google Analytics are registered trademarks of Google Inc. PostgreSQL is a
registered trademark of the PostgreSQL Community Association of Canada. Subversion is a trademark of the
Apache Software Foundation. Snowflake® is a registered trademark of Snowflake Inc. in the United States
and/or other countries. All other trademarks, servicemarks, registered trademarks, and registered servicemarks
are property of their respective owners.
Toad Data Point 6.4
User Guide
Thursday, January 9, 2025
 Contents

Copyright 2

Contents 3

Introduction 6
About Toad Data Point 6
Best Practices 7
Understand Toad's Workflow 8

Connect 9
Create Amazon Redshift Connections 9
Create ODBC Connections 10
Tips for Working with ODBC Connections 12
ODBC Features 12
Create Oracle Connections 12
Client Connection or Direct Connection 12
Configure the Oracle Instant Client 13
Create an Oracle Connection 13
Create Snowflake Connections 15
Authentication Notes 16
Limitations of Snowflake Native Connections 17
Create SQL Server and SQL Azure Connections 17
Microsoft SQL Azure Connections with Multi-factor Authentication 19
Limitations of Microsoft SQL Azure connections with Multi-factor Authentication 20
Create SharePoint Connections 20
SharePoint Connections with Multi-factor Authentication 21
Limitations of SharePoint connections with Multi-factor Authentication 22
Understand Toad Connections 23
Toad Open Connections 23
Sharing a Connection Among Windows 23
Troubleshoot SQL Server® Issues 24
Connection Issues 24
Create Databricks Connections 25

Understand 27
Determine the Best Method to Locate Objects 27
Object Explorer 27
Filter Databases and Objects 28
Types of Filters 28
Before Creating a Filter 29
Create Filters 29
Use Filters 30

Toad Data Point 6.4 User Guide

3
Contents
 Import/Export Filters 31
Tutorial: Understand a Table Using the Database Explorer 31
Search for Objects 33
Tutorial: Create a Database Diagram to Use as a Query Template 34
Browse Data in Related Tables 36

Query 38
Tutorial: Visually Build a Query 38
Create Cross-Connection Queries 40
Considerations and Limitations of Cross-Connection Queries 41
Reverse Engineer Queries 42
Considerations and Limitations in the Query Builder 42
Tutorial: Build a Subquery 44
Using Bind Variables 47
Use Quote Function to Substitute a String at Run Time 59
About Editing SQL 59
Configure Editor Options 61
Editor Shortcut Keys 61
View Result Sets 64
Visual Inspection Grid 64
Troubleshoot Data 64
Tips for Working with Data 64
Compare Differences in Data 66
Export Data with One Click Export 66
Share Excel Linked Queries 68
Publish Data to Intelligence Central 69
About Publishing Data 69
Prepare to Publish Data to Toad Intelligence Central 70
Publish to Toad Intelligence Central 72
Destination Database in Toad Intelligence Central 76
Variables in Views/Scripts and Publishing to Intelligence Central 78
Considerations and Limitations when Publishing to Intelligence Central 80
Publishing from Excel or Local Storage 83
Troubleshoot Publishing to Toad Intelligence Central 83

Report 86
Tutorial: Create a Toad Data Report 86
Step 1: Run the Toad Data Report Wizard 86
Step 2: Update Fields 87
Step 3: Use Styles to Format the Report Appearance 87
Step 4: Update Bands and Add Controls 89
Manage Reports 90
About Managing Reports 90
DB2 Reports 91
MySQL Reports 93
ODBC Reports 93
Oracle Reports 93
SQL Server Reports 95
Publish and Share Reports 97

Toad Data Point 6.4 User Guide

4
Contents
Automate 98
Getting Started with Automation 98
Introduction 98
The Automation Window 99
Create a Basic Script 101
Test and Run Your Script 102
Schedule Your Script 103
Automate Exporting to Excel Reports 104
Introduction 104
Create a Simple Excel Report 104
Create a Multi-Page Excel Report Using One Query 106
Create a Multi-Page Excel Report Using Multiple Queries 106
Schedule A Script 107
Automate Tasks 107
Use Database Automation Activities 110
Use File Automation Activities 118
Use System Automation Activities 122
Using Variables in Automation 127
How to Create Variables 128
How to Use Variables 128
Variable Scope 130
Execute and Schedule Automation Scripts 131

How to configure Toad to use Auth for sending SMTP Emails 133

About Us 138
Contact Quest 138
Technical Support Resources 138

Index 139

Toad Data Point 6.4 User Guide

5
Contents
 1
Introduction

About Toad Data Point

January 09, 2025

Toad® Data Point is a multi-platform database query tool built for anyone who needs to access data, understand
data relationships, and quickly produce reports.
With Toad Data Point, you can:
l Connect to almost any database or ODBC data source (see the Release Notes for a list of tested ODBC
connections)
l Write SQL queries and join data across all platforms
l Automate and schedule frequent and repetitive tasks
l Streamline data collection
l Collaborate with team members
l Export data in a variety of file formats
Toad Data Point provides a full-featured Database Explorer, Query Builder, and Editor for the following databases:
l Oracle®
l SQL Server® and SQL Azure
l IBM® DB2® (LUW and z/OS)
l SAP® ASE, IQ, SQL Anywhere, and HANA
l Teradata®
l MySQL
l Snowflake®
l Databricks
Toad Data Point also provides querying and reporting functionality via the following provider types:
l ODBC
l Business Intelligence and NoSQL data sources
l Microsoft® Excel®
l Microsoft Access®

ODBC Connections
The purpose of the ODBC provider is to offer basic connection and querying capabilities to any database that
supports an ODBC 3.0 or later driver. For a list of databases tested with the ODBC provider, see the System
Requirements in the Release Notes. This form of connectivity allows connections to databases such as Netezza,

Toad Data Point 6.4 User Guide

6
Introduction
IBM iSeries, Ingres, and Vertica™. Because this form of connectivity is generic, it is not full-featured and may not be
as robust as the fully-exploited providers for the other databases.
Business Intelligence and NoSQL Data Sources
Toad Data Point provides users with the ability to connect to non-traditional data sources, such as Business
Intelligence and NoSQL sources. You can connect to these data sources and explore data and objects, query data,
report results, and automate tasks.
The following data sources are supported:

Business Oracle Business Intelligence Enterprise Edition (OBIEE), SAP Business Objects™,
Intelligence Salesforce®, Microsoft SQL Server Analysis Services, Google Analytics™, OData, and
SharePoint®
NoSQL Azure Table Services, Cassandra™, Cloudera Impala, DynamoDB, HBASE™, Hive™,
MongoDB™

Best Practices
Toad provides tools that help you succeed in your query and reporting goals. Many of these tools are considered
Best Practices and are beneficial for individual users as well as managers. The following table describes the
benefits of using Toad to improve Best Practices:

Best Practice Benefit

One tool Connect to any database type for basic query needs. Using one tool for all platforms
makes it easier to learn and train others. Managers can create common files shared by all
users. This ensures consistency for connections, queries, and reports.
Shared connections Easily share connections between users by importing or exporting them.
Project Organize files and make them easily accessible to provide significant time savings. The
management Project Manager provides an easy way to group Database Diagrams, queries, and reports.
It also provides the following useful features:
l Create multiple projects to manage and track related tasks.
l Double-click files in the project to quickly load them for review or execution. You
can also add favorite web sites and directories.
l Email files to colleagues or send them to your local network share.
l Use the To-Do list to track items you need to complete.
Automation Tasks that are frequently repeated can be automated and scheduled using Automation.
You can schedule long running queries to execute during off-hours and can automatically
export the results to Excel. You can also add these tasks to the Project Manager, and then
double-click them to execute them. Automation offers an extension to your work day
without you having to be there.
Database Diagram Database schemas can be complex and difficult for new users to understand. The
Database Diagram displays the database relationships between tables to reduce the
learning curve. It also provides the following useful features:
l Add notes to help users learn more about specific tables and columns.
l Hide columns that are rarely used so users can focus on critical information.
l Add manual relationships to commonly used joins in queries.
l Save, share, or use Database Diagrams as a template for building queries.
Query Builder Create complex queries visually using the Query Builder. You can also:
l Reverse engineer a query from the Editor.

Toad Data Point 6.4 User Guide

7
Introduction
 Best Practice Benefit
l Create a cross-connection query between different database providers, such as
Oracle and DB2.
l Add Lookup tables to a column for easy filtering.
l Use bind variables to optimize a query.
l Annotate tables, relationships, or the actual diagram with notes.
l Save or share the query for future use.
l Add Query Builder files to the Project Manager for easy access during your
sessions.
Excel Build Excel reports and share them with colleagues. The One Click Export feature offers
options for quickly building Excel reports, including pivot tables for data manipulation. If
you want data in an exported report to reflect changes to the database, you can use the
One Click Export feature to export the underlying query with the report so it can be
refreshed with the latest updates.
Share Files and Store files and reports in libraries that can be shared with colleagues. You can specify a
Reports directory on a network share to share these files with others.

Understand Toad's Workflow

Most reporting projects have a general flow throughout their lifecycle that requires you to understand your data
sources, create queries, generate reports based on query results, and automate scripts for repetitive tasks.
l Understand—Successful queries begin with a clear understanding of the data source to which you connect.
If you understand the data and the objects, as well as their relationships to one another, you can build
informed queries that are effective and more optimized than you could without this understanding.
l Query—Queries are the most important, and often the most time-consuming, aspect of your project. Once
you have a clear understanding of the data source, you can begin to create your query.
l Report—Data retrieved from a query frequently needs to be shared with colleagues or management in the
form of a report, such as a pivot grid or chart.
l Automate—Queries and reports usually need to run on a daily, weekly, or monthly basis. With Automation,
you can create a workflow that includes connecting to data sources, importing/exporting data, executing
scripts, running queries, and building and distributing reports on a scheduled basis.

Toad Data Point 6.4 User Guide

8
Introduction
 2
Connect

Create Amazon Redshift Connections

Toad Data Point allows you to create a connection to an Amazon Redshift database using ODBC connectivity.

Note: You must have Amazon Redshift ODBC driver 1.3.1.1000 (or later) installed before a native
connection is created.

To install the Amazon Redshift ODBC Driver

Download and install the ODBC Driver from the Amazon Redshift Web site.

To create a new connection

1. Click on the Connections toolbar (ALT+F+N).

2. Select Amazon Redshift from the Group list box.

3. In the Create New Connection window configure the connection parameters.

General Tab Description

Driver name Uncheck the Use data source name checkbox and select Amazon Redshift (x64)
Driver or Amazon Redshift ODBC Driver (x64) depending on the installed version.
User Enter the username for your Amazon Redshift account.
Password Enter the password for your Amazon Redshift account.
Database Enter the name of the Database you would like to connect to or choose from the drop-
down list.
ConnectionSt Add the connection string for the Redshift cluster in the ConnectionString field.
ring Add the password string to the PWD= setting in the ODBC URL and paste the ODBC
URL to the ConnectionString field:
Driver={Amazon Redshift ODBC Driver
(x64)};Server=<server>;Database=<database>;UID=<user>;PWD=<
password>;Port=5439

4. Click Connect to save the connection and immediately connect to the database or click Save to save the

Toad Data Point 6.4 User Guide

9
Connect
 connection without connecting to the database.

Tip: Connections are stored in the connections.xml file and can be found by clicking the Application Data Directory
link in Help | About.

Create ODBC Connections

Toad Data Point allows you to create a connection to a database that supports an ODBC 3.0 or later driver. ODBC
connectivity provides basic querying capability, but may not be as full-featured as a native connection in Toad. For a
list of databases tested with the ODBC provider, see the Release Notes.
This form of connectivity allows connections to databases such as Netezza, IBM iSeries, Ingres, and Vertica.
Note: Distinct values are not available in Code Completion for ODBC connections.

DSN Architecture (Bitness) Must Match Toad

To create a successful ODBC connection, the architecture (bitness) of the driver in the specified DSN must match
the architecture (bitness) of Toad. For example, 64-bit Toad requires an applicable 64-bit driver and 32-bit Toad
requires an applicable 32-bit driver.

Toad Data Point 6.4 User Guide

10
Connect
To create an ODBC connection

1. Click on the toolbar (ALT+F+N+O).

2. Select ODBC Generic from the Group list box.

3. Use data source name—Select to display data source names. Clear this check box if you want to display
the driver names.

4. To create a data source to use in this connection, click in the Data Source Name field. Then click Add.

5. Select an ODBC driver from the list, and click Finish.

Note: If creating an Oracle ODBC connection, select the ODBC driver provided by Oracle. The Microsoft
ODBC for Oracle driver has less functionality than the driver provided by Oracle.
6. Specify the configuration properties required for the database in the Windows configuration dialog.

Notes:
l If you have an Oracle database with Unicode data, make sure to select Force SQL_WCHAR Support
on the Workarounds tab of the windows Oracle ODBC Configuration dialog. If you do not, you will not
be able to see the data.
l If creating a MySQL ODBC connection to use in a cross-connection query, you must specify a
database in the Windows MySQL ODBC Configuration dialog.
7. Specify the connection properties in the Create New Connection dialog. Review the following for additional
information:

General
Data source Select the driver or data source name you added in the previous steps.
name
User Enter the user name to use when connecting.
Password Enter the password to use when connecting.
Tip: After connecting, you can set a master password to further secure your
connection in Tools | Options| Environment | Security.
Database Select a database or schema. Click to create a temporary connection and
display available databases/schemas in the drop-down list.
Information
Data Source Displays the selected driver or data source name.
Name
Driver Displays the ODBC driver associated with the data source.
Advanced
Default Table Specify default options to use when creating a new table.
Column Column type—Select the default data type to use when creating a table.
Column length—For the selected column type, enter a default column length to
use.
Advanced Options Block Cursor Size—Specify the number of rows to return in a single fetch of data
from the result set.
Disable multi-threading—Multi-threading is disabled by default. Enable multi-
threading only if supported by the driver. Multi-threading can improve performance,
but can also result in unpredictable behavior if not supported by the driver.
Default: Selected
Category (Optional) Select an existing category or create a new one.

Toad Data Point 6.4 User Guide

11
Connect
 8. Click Connect to connect immediately while saving the connection information. Optionally, click Save to
save the connection without connecting.
Tip: Connections are stored in the connections.xml file and can be found by clicking the Application Data Directory
link in Help | About.

Tips for Working with ODBC Connections

Tip Description
Filter DSN by bitness To filter Data Source Names displayed in the New Connection dialog by the
bitness of Toad, go to Tools | Options | Database | ODBC.
Cache object metadata To cache object metadata for ODBC connections and retain it between sessions,
go to Tools | Options | Database | ODBC and select to enable disk caching.
Cached object metadata is retained until manually refreshed.
Refresh object metadata To refresh object metadata for all ODBC connections, in the Object Explorer right-
click an object and select Refresh All.
Right-click an object and select Refresh Schema to refresh only objects in that
schema.

ODBC Features
Feature Description
Bypass schema/instance For ODBC connections, in the SQL Editor you can bypass selecting a
selection in SQL Editor schema/instance and specify this information through the script instead. This is
useful if your script executes SQL against multiple schemas.
To use this method, select I will set schema/instance in the script from the
schema/instance drop-down list in the SQL Editor window. Then specify the
schema/instance through your script.
Note: When this option is selected, some code completion features, object
actions, and object information tool tips are unavailable for this SQL Editor
window.

Click here to view a video about creating connections in Toad Data Point

Create Oracle Connections

You can create a new connection, or connect to an existing connection from the Create New Connection window.
Click here to view a video about creating connections in Toad Data Point
Tip: You can configure Toad Data Point to accept connection information from Toad for Oracle.

Client Connection or Direct Connection

You can connect to your database using a native Oracle database provider in Toad using one of the
following methods:

Connection Method Advantages Disadvantages

Client connection Supports LDAP Requires an Oracle client

Toad Data Point 6.4 User Guide

12
Connect
 Connection Method Advantages Disadvantages
Client tab—Uses an installed Note: Toad supports Oracle clients and Oracle
Oracle Client or Oracle Instant Instant clients using version 9i or later.
Client to establish a connection to
the database from Toad.
Direct connection Does not require an This method only supports common data
Direct Connect tab—Uses TCP/IP installed Oracle Client types. Some advanced Oracle object types
to connect directly to the Recommended for cannot be retrieved when connecting using
database. connecting to Oracle 8i this method.
databases This method does not support LDAP. (To
connect using LDAP, use the Client
Connection method instead.)
This method cannot be used in a cross-
connection query.
Note: Quest recommends using one of the methods described in this table to connect to Oracle rather than an
ODBC driver. Because ODBC connectivity is generic, it is not full-featured and may not be as robust as the fully
exploited native database provider.

Configure the Oracle Instant Client

The Oracle Instant client installs only those files needed to connect to your database using OCI. Because this client
does not write to the registry (no ORACLE_HOMES) or add to the environmental path, you must manually set
environment variables so the client can be used with Toad.

To configure the Oracle Instant Client to work with Toad

1. Save the Oracle Instant Client files in any location on the computer where Toad is installed.
2. On your Windows desktop, right-click My Computer and select Properties.
3. Select the Advanced tab and click Environment Variables.
4. Click New under System Variables and complete the following:

Variable name Variable Value

PATH <Path where Instant Client is installed>;%PATH%

For Example: C:\Program Files\OracleInstantClient;%PATH%
TNS_ADMIN <Path where tnsnames.ora file is installed>

5. To use the Oracle Instant Client connection in a cross-connection query, you must also install an
Oracle ODBC driver. (While the Oracle Client full install includes an ODBC driver, the Oracle Instant
Client does not.)
Note: You cannot use an Oracle Direct Connection in a cross-connection query.

Create an Oracle Connection

To create an Oracle connection

1. Click on the toolbar (ALT+F+N).

2. Select Oracle from the Group list box.

3. Review the following for additional information:

Toad Data Point 6.4 User Guide

13
Connect
Login Tab Description
Database name Select the alias to use when connecting. This list displays aliases from LDAP, as
well as any local tnsnames.ora file.
When you initially open the connection dialog, Toad retrieves the list of LDAP names
from the LDAP server. Toad caches this list and displays it upon subsequent
requests, within one Toad session. To refresh the list, click .
Note: If you do not select a database, Toad uses the ORACLE_SID for the selected
home. If an ORACLE_SID value does not exist and you do not specify a database,
you cannot connect.
Password Enter the password to use when connecting.
Tip: After connecting, you can set a master password to further secure your
connection in Tools | Options| Environment | Security.
Connect as Select whether to connect as either SYSDBA or SYSOPER if you have the
appropriate permissions. Otherwise, use the default.
Schema Select a schema if you frequently work in a different schema than the one
associated with your user name.
Explain plan (Optional) Specify the location of an existing explain plan table to use instead of
table Toad automatically creating one for you when you execute a statement. This is
useful if you do not have necessary permissions to create explain plan tables or
want to use an existing table.
Category Select or create a category if you want to color code Editor tabs for a specific
connection. This can help differentiate between development and production
databases. You can also set an option to color code the Object Explorer pane and
object editor windows (Create, Alter, Drop, etc.). Click here to view a video of this
feature.
Save password Select this check box if you plan to use this connection in a scheduled Automation
script.
Note: This option is disabled if Toad was installed with Prohibit saving
passwords selected.
Oracle Client Tab Description
Current home Modify the Oracle home to use, if needed.
TNSNames Click this button to edit your local TNSNames file.
Editor Tip: You can press CTRL+F to open the Find/Replace window to locate entries in
the TNS Names Editor. Pressing F3 locates the next entry that matches your
criteria.
SQLNET Editor Click this button to edit SQLNET.ORA parameters. Before editing this file, you
should create a backup copy. See SQLNET.ORA Profile Parameters in the Oracle
documentation for more information.
Direct Tab Description
SID Enter the ORACLE_SID value for the database. This value is specified in the
registry under each installed home.
Advanced Tab Description
ODBC driver If an Oracle Client is installed, the default is the Oracle ODBC driver.
If an Oracle Client is not installed, the default is the Microsoft Access ODBC driver.
Tip: You can specify the ODBC driver to use for all connections for this database
provider in Tools | Options | Database | Oracle.

Toad Data Point 6.4 User Guide

14
Connect
 4. Click Connect to connect immediately while saving the connection information. Optionally, click Save to
save the connection without connecting.
Note: Go to Tools | Options | Database | Oracle to specify default options to use in Oracle connections.
Tip: Connections are stored in the connections.xml file and can be found by clicking the Application Data Directory
link in Help | About.

Create Snowflake Connections

Toad allows you to create a native Snowflake connection, which provides you with a full-featured Object Explorer,
Query Builder and SQL Editor.
Note: You must have a Snowflake ODBC driver 2.24.2 (or later) installed before a native connection is created.

To install the Snowflake ODBC Driver

Download and install the ODBC Driver from the Snowflake Web site.

To create a new connection

1. Click on the Connections toolbar (ALT+F+N).

2. Select Snowflake from the Group list box.

3. Enter the connection information in the Create New Connection dialog. Review the following for additional
information:
4. Review the following for additional information:

General Tab Description

Host Enter the name of the server hosting the database to which you want to connect.

Authentication There are four types of authentication that you can use to connect to Snowflake in
Toad. The required Login fields will change according to the type of authentication
you select. All the authenticators are located in a dropdown list:
l Snowflake – User account authentication
l SSO – Single Sign-On via external browser
l OAuth – Token-based authentication
l MFA - Multi-Factor Authentication
l Key pair - JSON Web Token (JWT)
User Enter the username for your Snowflake account
Password Enter the password for your Snowflake account
Token Enter your authentication token (Only visible when connecting via OAuth)
Private key path Enter your Private key (Only visible when connecting via Key pair)
Passphrase Passphrase is required only if the keys are encrypted (Only visible when
connecting via Key pair)

Toad Data Point 6.4 User Guide

15
Connect
 Port The default port number for Snowflake is 443
Optional A connection can be made without providing this information
Role Enter the user role that you would like to connect to
Warehouse Enter the name of the Warehouse you would like to connect to
Database Enter the name of the Database(s) you would like to connect to
Schema Enter the name of the Schema(s) you would like to connect to

4. Click Connect to save the connection and immediately connect to the database.
or
Click Save to save the connection without connecting to the database.

Authentication Notes
When using SSO or MFA, there is a way to maintain continuous and secure connectivity without needing to enter
login credentials for each connection attempt to Snowflake (as well as to prevent additional browser loading
whenever a new module is started in Toad Data Point). To achieve this, check whether the correct parameters have
been set in your account by executing the query below in your Snowflake cloud interface:
SHOW PARAMETERS IN ACCOUNT;

The output that will secure login credentials caching will have these values:

For SSO
allow_id_token = true;

For MFA
allow_client_mfa_caching = true;

If these values are set to false, then the following queries need to be executed:

For SSO:
ALTER ACCOUNT
SET allow_id_token = true;

For MFA:
ALTER ACCOUNT
SET allow_client_mfa_caching = true;

Note: If a Role or Warehouse isn’t chosen, the default settings from the Snowflake User account will be applied (it is
considered best practice that a user sets a default Role and a default Warehouse in their Snowflake user account).
Tip: It is strongly advised to use only the Snowflake user account authentication when creating Automation scripts
to avoid unnecessary workflow interruptions.

Toad Data Point 6.4 User Guide

16
Connect
Limitations of Snowflake Native Connections
l Connecting to Snowflake through Programmatic SSO (Okta only) is not currently supported.
l Semi-structured data types can be used for storing and analyzing purposes in Snowflake connections, but
are not supported in the Data Compare/Sync Wizard because Toad will not be able to generate valid sync
scripts unless they are modified before execution
l At the moment, there is no support for Code Snippets in Snowflake connections
l Publishing Snapshots, Views, Automation scripts, and Workbooks to Toad Intelligence Central cannot be
performed when using SSO, MFA, OAuth, Key pair authentication
Tip: Connections are stored in the connections.xml file and can be found by clicking the Application Data Directory
link in Help | About.

Create SQL Server and SQL Azure

Connections
You can create a new connection, or connect to an existing connection from the Create New Connection window.
See "Troubleshoot SQL Server® Issues" on page 24 for more information about troubleshooting connection issues.
Click here to view a video about creating connections in Toad Data Point
Note: For SQL Azure connections:
l To be able to utilize majority of Toad functionality please grant VIEW DEFINITION on the SQL Azure
databases you want to work with.
l You can login to Windows Azure Platform by selecting Tools | Cloud Computing | SQL Azure Portal and
providing your credentials.
l Make sure that no Azure firewall rule restricts your IP address from having access to Azure server. See
"Create and Alter Azure Firewall Rules" in the online help for more information.

To create a new connection

1. Click on the toolbar (ALT+F+N).

2. Select Microsoft SQL Server or Microsoft SQL Azure from the Group list box.
3. Review the following for additional information:

Login Tab Description

Server name Enter the name of the server to use when connecting.
Click to display any servers running SQL Server that are currently active on the
network.
Notes:
l For SQL Azure connections, enter InstanceName.database.windows.net
in this field.
l For named instances, enter the MachineName\InstanceName in this field.
Authentication Select the type of authentication to use for this connection.
l You can connect to an Azure SQL database with Active Directory
authentication (either Active Directory Integrated Authentication or Active

Toad Data Point 6.4 User Guide

17
Connect
 Directory Password Authentication). Client configuration is required. See
https:// docs.microsoft.com/en-us/azure/sql-database/sql-database-aad-
authentication-configure#configure-your-client-computers
l You can also connect to an Azure SQL database with Active Directory
Universal with MFA.
Note: You cannot create a SQL Azure connection using Windows Authentication.
Password Enter the password to use when connecting.
Tip: After connecting, you can set a master password to further secure your
connection in Tools | Options| Environment | Security.
Application ID Enter the application ID located in App registrations in Azure Active Directory.
(This is a mandatory field when connecting to Microsoft SQL Azure via MFA.)
Redirect URI Enter the appropriate redirect URI.(This is an optional field when connecting to
Microsoft SQL Auzre via MFA which can be left empty unless you have specific
requirements for configuring the Redirect URI.)
Category Select or create a category if you want to color code Editor tabs for a specific
connection. This can help differentiate between development and production
databases. You can also set an option to color code the Object Explorer pane and
object editor windows (Create, Alter, Drop, etc.). Click here to view a video of
this feature.
Encrypt Select this option to enable encryption for the current connection provided that the
connection certificate has been already installed on the server.
Advanced Tab Description
Network protocol Select one of the following:
l Shared Memory
l TCP/IP
l Named Pipes
Note: When establishing Named Pipes connection "Poll service status in
connection list" option should be cleared.
Parameter You can add parameters to the connection. Toad includes the Application Name
Name/Value parameter with value Toad Data Point by default.
OBDC driver Select an ODBC driver to use for this connection when creating cross-connection
queries.
Tip: You can specify the ODBC driver to use for all connections for this database
provider in Tools | Options | Database | SQL Server.

4. Click Connect to save the connection and immediately connect to the database.
or
Click Save to save the connection without connecting to the database.
Tip: Connections are stored in the connections.xml file and can be found by clicking the Application Data Directory
link in Help | About.

To import connections (including Central Management Server connections)

1. Click .

2. Specify the location where the connections currently reside.

Note: Review the following for additional information on CMS connections:

Toad Data Point 6.4 User Guide

18
Connect
 l To import CMS connections, select a server from the list where the CMS is registered. The
connection to this SQL Server instance should be created beforehand.
l CMS is available beginning with Microsoft SQL Server 2008.
l User should have an appropriate login (Windows Authentication and grant to connect to selected
server are required) created on the server to which he connects via CMS.
3. Select the connections to be imported.

Microsoft SQL Azure Connections with Multi-factor

Authentication
To create this type of connection to Microsoft SQL Azure, Active Directory Universal with MFA needs to be
selected from the Authentication field dropdown.

The next step is to enter a user name which has been configured for multi-factor authentication and put in the
appropriate App ID. After filling out the required information, click on Connect.

The step that follows will enable you to select a user from the list or add another one by clicking on Use
another account.
After that, you will be prompted to enter your credentials and depending on the type of authentication that the
account has been configured with, you will undergo an additional authentication step, such as the one in the
image below.

Toad Data Point 6.4 User Guide

19
Connect
After completing the final authentication step, a successful connection to Microsoft SQL Azure will be made.

Limitations of Microsoft SQL Azure connections with

Multi-factor Authentication
l Publishing Snapshots, Views, Automation scripts, and Workbooks to Toad Intelligence Central cannot be
performed when using Active Directory Universal with MFA type of authentication.

Create SharePoint Connections

Toad allows you to connect to SharePoint via OData service to view and query SharePoint lists as tables. Toad
supports SharePoint 2010 and 2013. You can also create a connection to SharePoint Online.

Note: This feature is available in the Toad Data Point Professional Edition only.

To create a SharePoint connection

1. Click on the Connections toolbar (ALT+F+N).

2. Select SharePoint from the Group list.

3. Enter the connection information in the Create New Connection dialog. Review the following for additional
information:

URL Enter the full URL address to the SharePoint OData service endpoint for the
SharePoint site to which you want to connect.
The URL address should have the following format:
http://<servername>/_vti_bin/listdata.svc
Authentication Select the type of authentication to use for this connection. Toad offers two options:
l Basic Authentication
l Active Directory Universal with MFA

Toad Data Point 6.4 User Guide

20
Connect
 The required login fields will be enabled or disabled according to the authentication
type that is chosen.
Username For SharePoint—Enter your Windows user name to use when connecting to this
SharePoint site.
For SharePoint Online—Enter the user name to use when connecting to this instance
of SharePoint Online.
Password Enter the password to use when connecting.
Tip: After connecting, you can set a master password to further secure your
connection in Tools | Options| Environment | Security.
SharePoint Select this option if creating a connection to a SharePoint Online instance.
Online
Category (Optional) Select an existing category or create a new one.
Note: In NoSQL and Business Intelligence connections, Toad automatically saves the password in the
connections.xml file as obfuscated text, as well as in Toad. To add additional password security, use
Toad's Master Password feature.

4. Click Connect to connect immediately while saving the connection information. Optionally, click Save to
save the connection without connecting.
5. Upon creating an initial connection, Toad automatically maps the data source objects. This process
runs in the background, and until it is finished, you may experience a delay when attempting to access
these objects.
Tips:
l Connections are stored in the connections.xml file and can be found by clicking the Application Data
Directory link in Help | About.
l You can specify a proxy server to use for Business Intelligence and NoSQL connections at Tools | Options
| Database | Data Services.

SharePoint Connections with Multi-factor

Authentication
To create this type of connection to SharePoint, Active Directory Universal with MFA needs to be selected from
the Authentication field dropdown.
The next step is to enter a user name which has been configured for multi-factor authentication and
click on Connect.

Toad Data Point 6.4 User Guide

21
Connect
Toad Authentication Window will appear allowing you to select either Windows Authentication or AzureADTrust
for the type of credentials which will be used for the connection.
The step that follows will enable you to select a user from the list or add another one by clicking on Use
another account.
After that, you will be prompted to enter your credentials and depending on the type of authentication that the
account has been configured with, you will undergo an additional authentication step, such as the one in the
image below.

After completing the final authentication step, a successful connection to SharePoint will be made.

Limitations of SharePoint connections with Multi-factor

Authentication
l Publishing Snapshots, Views, Automation scripts, and Workbooks to Toad Intelligence Central cannot be
performed when using Active Directory Universal with MFA type of authentication.

Toad Data Point 6.4 User Guide

22
Connect
 l Setting a default SharePoint connection in Tools|Options|Environment is currently available only for
Basic Authentication

Understand Toad Connections

Review this topic to learn about open database connections in Toad.

Toad Open Connections

In Toad, an open connection in the Connection/Navigation Manager represents one or more actual connections to
the database or data source. When you open a database connection in Toad:
l Toad creates an initial database connection for general use.
l Toad also creates a separate database connection for each Editor (the first time you execute a statement)
and for each Data tab in the Database Explorer (the first time you select the tab and query data).
The connection associated with the window (or tab) remains open until you close the window. If a
window has an associated connection, but a statement is not currently executing, the connection is in
an inactive state.

To view information for an open connection

l To view the number of actual database connections that exist for a Toad connection, including the number of
active connections, place the cursor over the connection in the Connection/Navigation Manager pane or in
the connection drop-down list.

To use the Background Processes pane

l When a statement is executing, a progress bar displays in the Status bar located at the bottom of the Toad
application window. Click the executing progress bar to open the Background Processes pane.
l To cancel statement execution, use the Background Processes pane.
Note: It is recommended that you cancel only your own processes, because terminating Toad activities
could cause unexpected behavior.

Sharing a Connection Among Windows

You can instruct Toad to share an open connection among windows. In this way, the Editor windows and Data tabs
can share the same connection when the connection is inactive.

Toad Data Point 6.4 User Guide

23
Connect
To share a database connection among windows in Toad
1. Select Tools | Options | Editor | General.
2. Clear the checkbox for the Use individual connection for each open editor option.
3. When this option is not selected, Toad attempts to use a single connection for any new windows or query
execution. For example, if you share a database connection, you can execute a query in the Editor, execute
another query in a new Editor, and then select the Data tab from the same connection.
If the single connection is in use when Toad attempts to execute it, the following occurs:
l (DB2 and Oracle only) If your database supports executing multiple queries simultaneously on a
shared connection, the query is placed in a queue and executes as soon as the current operation
completes.
l If your database does not support executing multiple queries on a shared connection, a dialog
prompts you to either permanently associate the window with a new database connection, cancel
the previous operation, or add the query to a queue until the current operation completes, and the
execute it. If you place multiple queries in the queue, each one executes in the order that it was
added to the queue.
Note: Although you can share a database connection, any commits apply to the selected window only.

Troubleshoot SQL Server® Issues

Connection Issues
Review the following solutions to help troubleshoot SQL Server connection issues in Toad.

Issue Cause/Solution
Cannot specify a port number Currently, Toad does not have a separate field for specifying a port number.
when connecting Solution:
You can connect if you know the port number for the instance by entering the
following in the Server name field on the New Connection window:
ServerName,Port
or
IPAddress,Port
For example:
10.1.29.129,1234 (where 1234 is the port number of the instance)
Cannot connect to a remote Remote connections may not be enabled in SQL Server 2005. Also, named
instance in SQL Server 2005 instances are configured to use dynamic ports and select an available port
once the SQL Server Service starts. If you are connecting to a named instance
through a firewall, configure the Database Engine to listen on a specific port,
so that the appropriate port is opened in the firewall.
Note: Remote connections are disabled by default in the Express and
Developer editions of SQL Server 2005.
Solution:
To enable remote connections, see "How to configure SQL Server 2005 to
allow remote connections" at: http://support.microsoft.com/kb/914277
"Path is not of a legal form" error You attempted to migrate settings from a previous install of Toad to the current
message when connecting install, but the settings file (InitialSqlServer.Sql) for the previous install was
or either removed, moved, or corrupt.

Toad Data Point 6.4 User Guide

24
Connect
 Issue Cause/Solution
"Could not find a part of the path You may also receive this error if you installed Toad and migrated settings
C:\Program Files\Quest from a previous version, and later uninstalled the previous version.
Software Solution:
\Toad Data
You must specify a valid location for the InitialSqlServer.Sql file in Toad.
Point\Template\Scripts\
InitialSqlServer.Sql" error
message when connecting To specify a valid location
1. Open the SQL Server options page under Tools | Options | Database
| SQL Server.
2. Specify the location for the new install's InitialSqlServer.Sql file in the
Run Script on new connection field.
By default, this file is located in the following directory:
C:\Program Files\Quest Software\Toad Data Point version \Templates\Scripts
Note: Clicking may result in a "Path not found error". If you receive this
error, manually enter the location in the field.
Cannot establish Named Pipes If you are trying to connect to the server using Named Pipes, "Poll service
connection status in connection list" option should be cleared.
Solution:
1. Open the options page under Tools | Options | Database | General.
2. Clear Poll service status in connection list checkbox.
Cannot access some To utilize majority of Toad functionality VIEW DEFINITION grant is required for
functionality when working with those databases you want to work with.
Azure connection.

Create Databricks Connections

Toad allows you to create a native Databricks connection, which provides you with a full-featured Object Explorer,
Query Builder and SQL Editor.

Note: You must have a Simba Apache Spark ODBC Connector 2.8.2 (or later) installed before a native connection
is created.

To install the Simba Apache Spark ODBC Connector

Download and install the ODBC Connector from the Databricks Web site.

To create a new connection

1. Click on the Connections toolbar (ALT+F+N).

2. Select Databricks from the Group list box.

3. Enter the connection information in the Create New Connection dialog.
4. Review the following for additional information:

General Tab Description

Toad Data Point 6.4 User Guide

25
Connect
 Host Enter the server where the Databricks instance is hosted.

Authentication Select the type of authentication to use for this connection. Toad offers two
options:
l Personal access token
l OAuth 2.0 token
Port The default port number is 443
HTTP Path Enter the endpoint used to route queries to the appropriate cluster or resource.
Token Enter your authentication token
Optional A connection can be made without providing this information
Catalog Enter the name of the Catalog you would like to connect to

4. Click Connect to save the connection and immediately connect to the database.
or
Click Save to save the connection without connecting to the database.

Tip: Connections are stored in the connections.xml file and can be found by clicking the Application Data Directory
link in Help | About.

Toad Data Point 6.4 User Guide

26
Connect
 3
Understand

Determine the Best Method to Locate

Objects
Toad offers a number of features to assist you in locating objects. The following table provides guidelines based on
typical use cases:

If you know the.... Use this...

Column name, but do not know the table name Use Object Search
String of DDL for an object
Dependency for an object Uses and Used By tabs in the Database Explorer, and
then drill-down to the object from there
Criteria for excluding objects Filter feature in the Object Explorer

Object Explorer
You can view database objects for the current connection with the Object Explorer. This feature is useful for
dragging and dropping database objects into an open Editor, Query Builder, or other document window. Right-click
the objects in the Object Explorer to access additional features such as multiple tasks and actions.

To open the Object Explorer

1. Select View | Object Explorer.
2. Review the following for additional information:

Schema (first field) Select the schema you want to view. You can filter the list of schemas that
display. See "Filter Databases and Objects" on page 28 for more information.
Object Filter (second You can quickly set a filter in this field. To do so, enter a name or partial name of
field) an object and press ENTER. Any advanced object filters you set display in this
field.
You can also perform advanced filtering based on case sensitivity, statistics, or
other options. See "Filter Databases and Objects" on page 28 for more
information.
Tip: You can enable an option to filter using regular expressions in Tools
| Options | Explorer | General.
Note: For tables and views, the columns, data types, and comments display at the bottom of this window.

Toad Data Point 6.4 User Guide

27
Understand
Tips:

l
To navigate to a previously selected object, click . You can also click to move to the next
selected object.
l To locate objects including procedures, functions, views, variable names and comments in an object's
source code, use Object Search. See "Search for Objects" on page 33 for more information.

l
To use a different method to select objects such as tabs, drop-downs, or a tree view, click .
l To generate reports, export data, create SQL statements, or send objects to the Project Manager, SQL
Modeler, or Master Detail windows, right-click the objects and select an option.
l To open the Background Processes window and cancel a query, click the progress bar at the bottom of
the window.

Filter Databases and Objects

Toad allows you to filter the list of databases or objects that display in the Object Explorer. You can use filters to
reduce the number of databases and/or objects to work with.

Types of Filters
You can create a Quick filter by entering a search string in the object filter field. You can also use the Filter dialog

(click ) to choose additional filter options. If you use the Filter dialog, you can also name and save your filter.

Filter Type Description Behavior

Quick (unnamed) Can be used to quickly filter objects for Applies to the current schema (or to
a single connection or schema. objects in the current schema) only.
Create a Quick filter by entering a Once applied, a Quick filter is in
search string in the object filter field. effect on that connection when you
reconnect or relaunch Toad.
Because Quick filters are not named,
they cannot be applied to other
connections by selecting from the
filter list. You must recreate the
Quick filter for each
connection/schema.

Detailed (unnamed) Use like a Quick filter, but add more -- same behavior as a Quick filter --
detail by using the Filter dialog to enter
filter criteria.
Use this method to create an unnamed
database filter.

Named Create and save named filters to reuse Once applied, a Named filter is in
and to apply to other connections of effect when you reconnect or
the same data source type. relaunch Toad.
Can be re-used on other
connections of the same data source
type (DBMS).

Note: If you rename a table that has a filter defined, it results in an orphaned filter. Orphaned filters are automatically
removed unless you used a tool other than Toad to remove them.

Toad Data Point 6.4 User Guide

28
Understand
Before Creating a Filter
A filter is associated with a specific list of objects from a specific database. Consider the following guidelines when
setting filters:
l A filter applied to a set of objects such as tables in one specific database connection is not automatically
applied to the list of objects in another database connection.
l A filter applied to a list of tables on one schema in the database is not automatically applied to the list of
tables for a different schema in the same database.
l A filter set for a specific schema or object is applied when altering objects, using the SQL Editor, Master
Detail Browser, etc., for that schema or object.
Tip: To apply any currently-set filters to the code completion list in the editor, select the Apply explorer
filters option in Tools | Options | Editor | Code Completion.

Create Filters
To create a Quick filter (objects only)
l Enter a search string to use for filtering in the object filter field (below the database field) in the
Object Explorer.
Note: As you enter a search string, the currently specified Search Condition displays in a blue banner below
the object filter field. This Search Condition will be used in your filter. To change the Search Condition, click

and select a different one from the Search Condition drop-down.

To create a Detailed filter

1. Click beside the database or object filter fields in the Object Explorer.

2. Specify criteria for the filter in the Properties tab. Review the following for additional information:

Search String Enter a text string and search characters to use for filtering.
Search Condition Select a search type to use in this filter.
Note: You can set a default value for this field in Tools | Options |
Explorer | General. If you don't specify a default value in Options, this field
defaults to the search type you specified the last time you created or
modified a filter (includes previous sessions).

Toad Data Point 6.4 User Guide

29
Understand
 Tip: To include in your text string certain characters normally used in
search logic, such as the underscore (_), use one of the following Search
Conditions:
l Contains (Exact)
l Starts With (Exact)
l End With (Exact)
Case Sensitive In DB2 connections, this property is set to True by default.

3. To create custom SQL, select the SQL tab and then select the Enable custom SQL option. Modify the
existing SQL. (The existing SQL reflects the criteria you specified in the Properties tab).
4. Select the Preview tab to review the list of databases or objects that are selected based on your filter criteria.

To create a Named filter

1. Click and create a Detailed filter (see above).

2. Enter a name for the filter and click . Creating a name for the filter allows you to use it on other database
connections.

Creating a new Quick filter from a current filter

l If you apply a filter and then enter a search string into the object filter field without completely clearing the
current filter in the field, the new search string is applied, as well as the properties specified for the old filter.
l If you apply a filter and then completely clear the object filter field before entering a new search string, all
properties return to the default values.
Note: The Search Condition also returns to the default value. This default is either the Search Condition you
specified the last time you created or modified a filter or it is the new default you specified in Options.

Use Filters
To use Named filters

l
To apply a Named filter, click the down-arrow beside and select the filter from the drop-down list.

l
To clear a Named filter, click the down-arrow beside and select <None>.

l To determine if a filter is applied, observe the filter icon.

Icon Indicates
No filter is applied (all databases/objects display).

A filter is applied.

Toad Data Point 6.4 User Guide

30
Understand
Import/Export Filters
You can export all named filters and you can export all filters defined for the current connection.

l
To export Named filters or all filters defined for the current connection, click the down-arrow beside in
the database field and select Export Filters.

l
To import filters previously exported from Toad, click the down-arrow beside in the database field and
select Import Filters. Browse to and select the filter file (.tfp) to import.

Tip: Click in the Database Explorer (Viewer) toolbar to clear all database, object, and data filters.

Tutorial: Understand a Table Using the

Database Explorer
Scenario
You need to build a query listing customers and want to know more about the table
before writing the query.
Note: This exercise uses the Toad Sample Database. This connection does not have
the relationship and script tabs. The tabs available can differ depending on the type of
connection.

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

To understand a table using the Database Explorer

1. Connect to the Toad Sample (Access) database in the Navigation Manager.
2. Right-click the CONTACT table and select View Details.
3. View information for the following:
a. Click the Indexes tab and view the index information.
b. Click the Primary Keys and Foreign Keys tabs to view relationships.
4. Click the Data tab.

5. Place the cursor over the LAST_NAME column, click , and select Acton.

Toad Data Point 6.4 User Guide

31
Understand
 The only row that now displays is the one with Acton as the last name.

6. Click to the right of the LAST_NAME column again and select All to clear the filter.

7. Click the CONTACT_ID column to sort the rows in ascending or descending order.

8. Click in the top left of the Data tab.

9. Create the following filter in the Where Clause tab:

CONTACT_ID > 2000
The filter string displays at the bottom on the grid. Click to remove the filter.

10. Select a row of data in the grid. Notice that the row number changes in the bottom right of the status bar:

11. Under the SEX column, right-click the area below the last row in the grid that says "Right-click to add column
totals" and select Count. The total number of rows that have either a M or F value display.

12. Click to the right of the SEX column and select F. The count value updates based on the new filter.

13. Click to the right of the SEX column again and select All to clear the filter.

14. Select the SEX column header and drag it the area above the columns that says "Drag a column header
here to group by that column":

Toad Data Point 6.4 User Guide

32
Understand
 You can expand each group to view the row data.
15. Switch the default view:

a. Right-click within a group or row on the grid and select Show | Card View.
b. Scroll through some records.
c. Click Customize in the Card View window.
d. Click Filter beside the ADDRESS_ID column and select 100. Notice how the card view is filtered, but
not the grid view.
e. Close the Card View window.
16. Edit a row:
a. Select a row in the FIRST_NAME column and edit the value.
b. Press ENTER to update the row.
c. Go to the BIRTH_DATE column and use the date picker to change the date.
Each data type have an appropriate editor.
Note: If you are using Oracle or a database that does not have auto commit on, you will need to
press Commit at the left hand bottom of the window.
17. Update the number of rows in in the data:

a. Select Tools | Options | Database | General from the menu.

b. Enter 50 in the Number of rows to initially fetch in data tab.
c. Click OK.
d. Press F5 in the data grid. Notice the bottom right status bar now says "1 of 50 (scroll for more)".
The option you set controls the number of rows fetched in the Data tab.
e. Click the scrollbar and hold down to retrieve more.
f. Right-click the grid and select Read All Rows.

Search for Objects

You can quickly locate an object rather than browsing through a list of objects in the Database Explorer orObject
Explorer. Object Search locates the following:

Toad Data Point 6.4 User Guide

33
Understand
 l Names of objects for a string.
l Text in objects that include source code, which typically includes procedures, functions, triggers, data types,
and views. However, the database provider determines which objects include source code.
l Variable names and comments in the object's source code.
l Column names for tables and views.

To search for objects

1. Click on the toolbar (F4).

2. Specify search criteria and click Search.

Note: To append additional search results to the current search, click Append instead. If you click Search,
new results replace the previous search results in the data grid.

Tips:
l To remove objects from the search results, right-click the objects in the data grid and select Remove Items.
This is useful if you want to narrow the list of results to relevant objects.This does not remove the objects
from your database.
l To create a DDL script from the search results, right-click the data grid and select Create DDL Script. You
can then paste the results into the Editor.
l To perform any actions available for the object, such as creating, altering, dropping, exporting, and
generating reports or SQL, right-click the results in the data grid and select an option.

Tutorial: Create a Database Diagram to Use

as a Query Template
Scenario
Your company creates several daily and weekly reports regarding customer sales. You
need to create a Database Diagram that can be used as a template for these queries.

To create a Database Diagram to use as a query template

1. Connect to the Toad Sample (Access) database in the Navigation Manager.
2. Select Tools | Diagram.
3. Drag the CONTACT table in the Object Explorer to the Diagram pane.

Toad Data Point 6.4 User Guide

34
Understand
4. Right click the Diagram pane and clear Show Dependencies to hide the Referenced Objects at the bottom
of each table/view, as illustrated in the following screen capture.

5. Right-click the ORDERS table and select Notes.

6. Enter the following note in the window for other users:
The shipping and billing ADDRESS_ID foreign key relations are not needed in most queries and should be
hidden when building a query.
7. Because it is against company policy to display sex and a home phone number in reports, right click on each
of these columns in the CONTACT table and select Hide Column.
Tip: If you have a large number of columns you need to hide, you can right-click a table and select Manage
Hidden Columns to quickly select them from a list.

Toad Data Point 6.4 User Guide

35
Understand
 8. Click Add to Project from the wizard bar at the bottom of the window and enter DBDiagram_Test asthe
name for the file. This adds the file to the current project in the Project Manager under a Diagrams folder.
9. Close the Database Diagram window and open the Project Manager window.
10. Expand the Diagrams folder and double-click DBDiagram_Test to reload the file in a new Database
Diagram window.
11. To share your project, hidden columns, and notes with colleagues, complete the following:
a. Select Tools | Options | Environment | Network Share, and navigate to a shared drive where you
want to store the Project Manager file.
b. Select Database | Cache from the open Options window and set the following options:

Object An notation Description

Cache
Share object Select this checkbox and enter a shared drive location where users can
annotations access this file.
Share hidden column Select this checkbox.
settings

c. Provide the location of the Network Share and Object Annotation Cache settings to colleagues who
need to use this diagram.

Browse Data in Related Tables

Use the Master Detail Browser to browse data in parent tables and drill-down to data in related child tables. This is
useful when you need to analyze or edit data. For example, suppose you need to view a list of all Nick's Flix stores
and the inventory of comedy movies at each store. Using the Master Detail Browser, you specify the table that
contains the list of stores as the parent table and specify the table that contains comedy movies as the child table to
quickly locate comedies and analyze the inventory.

To browse tables using the Master Detail Browser

1. Select Tools | Master Detail Browser (ALT+T+A).

2. Drag one or more tables from the Object Explorer, Object Search, or Project Manager window to the
Diagram pane at the top of the Master Detail Browser. Press CTRL+click to select multiple tables at once.
Notes:
l A line between tables indicates any existing relationships between the selected tables.
l At least one parent table (table without any join arrows pointing to it) must be added to the window or
an error message displays when returning results.
3. Join Columns.
4. Select the checkbox beside each column you want to include.
Note: Any columns included in a join are automatically included in the results.

5. Click (F9) to retrieve data for the parent table initially. To view data in a child table, click + to the left
of each row.
or

Click to retrieve data in the parent and child tables. If there is more than one child table, a tab displays
for each child table.

Toad Data Point 6.4 User Guide

36
Understand
 6. From the data grid, you can:
l Edit the data and commit your changes to the database.
l Right-click the data grid and select Send To | option to add the data to a report, chart, or pivot grid.
Tips:

l
Click to align tables and snap them to the grid.
l Select the parent table from the drop-down list in the toolbar to view data for a parent table if there are
multiple parent tables.
l Click X on the table you want to remove in the Diagram pane to remove a table and any conditions (e.g., join,
where clause) associated with the table.

Toad Data Point 6.4 User Guide

37
Understand
 4
Query

Tutorial: Visually Build a Query

The Query Builder enables you to create a query without writing or editing SQL statements. Even if you are familiar
with SQL, the graphical interface makes it easier to create relationships and visualize the query.
Note: This procedure does not cover all of the possible steps of visually building a query. It only covers the steps
required to build a query for the scenario. See Building Queries Visually for more information.

Scenario
Your company wants to improve its global sales, starting with countries where it has a
customer base but generally low sales. You need to create a query that identifies
countries with less than $500,000 in sales for each of the last four fiscal years.
Note: This scenario uses the SH (Sales History) sample schema that comes with Oracle
10g so you can follow the procedure.

To visually build a query based on this scenario

1. Connect to the database and select Tools | Query Builder | Query Builder.
2. Select the SH schema in the Object Explorer.
3. Drag the following tables from the Object Explorer to the Diagram pane: TIMES, SALES, CUSTOMERS,
and COUNTRIES.

Tips:
l Select Query Builder | Arrange Tables (ALT+Q+A) to have Toad arrange the tables in the
Diagram pane.
l You can press CTRL and select a table, and then drag the table into the Editor pane. Click the list to
create statements.
l You can also press CTRL and select all of the tables in the Object Explorer, and then drag them into
the Diagram pane. Toad automatically arranges the tables.
4. Add the following columns to the query by clicking each column:

Table Column
COUNTRIES COUNTRY_NAME
TIMES FISCAL_YEAR
SALES AMOUNT_SOLD

Tips:

l
To view details for a table in the Diagram, click (F4). If a table is not selected, details for the last
selected table display.

Toad Data Point 6.4 User Guide

38
Query
 l
To remove a column from the query, click or drag column off grid.

5. Complete the following steps to group the data and sum the sales:
a. Select the Group By field in the COUNTRY_NAME column, and then click +. The Group By field is
in the Criteria pane, which is below the Diagram pane.
b. Select the Group By field in the FISCAL_YEAR column, and then click +.
c. Select the Aggregate Function field in the AMOUNT_SOLD column and select Sum. This sums all
of the sales per fiscal year per country.
Note: If you select a group by clause or aggregate function for a column, the rest of the columns in
the query must also have a group by clause or aggregate function.
6. Complete the following steps to select the fiscal year range:

a. Select the Where Condition field in the FISCAL_YEAR column and click . The Where Condition
window displays.

b. On the Form tab, select BETWEEN in the Operators field.

c. Select 1998 in the first Constant field.
d. Select 2001 in the second Constant field and click OK.
Note: The SH schema does not have current data. The 1998-2001 years provide the best data for
the purposes of this scenario.
7. Complete the following steps to select countries with less than $500,000 in sales:

a. Select the Having Condition field in the AMOUNT_SOLD column and click . The Having
Condition window displays.
b. On the Form tab, select < in the Operators field.
c. Select Sum in the Aggregate field.
d. Enter 500000 in the Constant field and click OK.
8. Select Ascending in the Sort field of the COUNTRY_NAME and FISCAL_YEAR columns.

9. Click .

The query results display in the Results tab. See "View Result Sets" on page 64 for more information.

10. To save the query, right-click the Query Builder tab and select Save File.
You can save a query 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.

Toad Data Point 6.4 User Guide

39
Query
See Tutorial: Build a Subquery for a tutorial on creating a subquery.

Create Cross-Connection Queries

Use the Cross-Connection Query Builder or the Cross-Connection Editor to create a query that combines data from
multiple, dissimilar databases. For example, you can create a query that joins managers from a SQL Server
database to their employees in an Oracle database. (This is also often called a heterogeneous or cross-database
query.) Toad uses an enhanced query engine to implement advanced execution processes that are specially
designed to improve the performance of cross-connection queries.
Before creating a cross-connection query using an Oracle, Teradata or MySQL ODBC connection, please see the
special considerations listed at the end of this topic.

Note: This feature is available in the Toad Data Point Professional Edition only.

To build a cross-connection query visually

1. Select Tools | Build Query | Cross-Connection Query Builder.
Note: A blue banner along the right side of the Diagram pane indicates that you are in Cross-Connection
Query mode.
2. Drag tables and views from the Object Explorer to the Diagram pane.
3. Complete the query in the same manner as you would a non-cross-connection query, but note the following
exceptions.

Object Explorer Feature In Cross-Connection Query Mode...

Options Connection Treelist is the only view option available. In this option,
connections, databases, tables, and views are displayed in a treelist in
the Object Explorer window.
Filters Filters are especially useful in cross-connection queries to reduce the
number of objects displayed in the treelist in the Object Explorer
window.

Note: Review the "Considerations and Limitations of Cross-Connection Queries" section below.

To compose a cross-connection query in the editor

1. Select Tools | Edit | Cross-Connection SQL Editor.
Note: A blue banner along the right side of the Editor pane indicates that you are in Cross-
Connection Query mode.
2. Compose the SQL statement in the same manner as you would a non-cross-connection query, but note the
following exceptions.

SQL Editor Task In Cross-Connection Query Mode...

Naming tables and views To reference a table or view, you must use a cross-connection fully-
qualified name. The format for a cross-connection fully-qualified name
depends on the database. The following is an example using the SQL
Server format:
<database connection
string>.<database>.<schema>.<table>

Toad Data Point 6.4 User Guide

40
Query
 SQL Editor Task In Cross-Connection Query Mode...
'sqlserverconn'.database.dbo.table
Note: For the database connection string, use the connection string
displayed in the Navigation Manager window for each connection (or in
the Name field in Connection Properties).
Tip: Copy and paste a SQL statement from a single connection editor
to a cross-connection editor and Toad automatically changes the
syntax of the statement to include the cross-connection fully-qualified
names.
Using alias names You must use alias names for tables and columns in a cross-
connection query.
Using code completion In Cross-Connection Query mode, the scope of available options in
code completion expands to include the databases, tables, and views
for all the open database connections.
Tip: To expand/collapse a database node in the code completion list,
use CTRL+left arrow and CTRL+right arrow.

Tips:
l In a cross-connection code completion list, the selected database displays a descriptive tooltip containing
database and connection information.
l Use the Database Diagram tool to create and save cross-connection relationships, and then send the tables
with their relationships already created to the Query Builder.

Considerations and Limitations of Cross-Connection

Queries
Review the following considerations and limitations before creating a cross-connection query.

Consideration/Limitation Description
Performance concerns A cross-connection query may take longer to execute than a query against a
single database because result sets from each database are retrieved and
combined to generate a single result set.
In the Professional edition of Toad Data Point, the enhanced execution engine
utilizes several optimizing techniques to improve the performance of cross-
connection queries.
ODBC driver support An ODBC driver can be selected for a single connection through each
connection's Properties dialog or globally through Tools | Options |
Database.
Using an Oracle connection in a To use an Oracle connection in a cross-connection query, you must install an
cross-connection query Oracle ODBC driver.
Note: The Oracle Full Install client includes an ODBC driver. However, if you
are using the Oracle Instant client (which may not include an ODBC driver),
you may need to install an Oracle ODBC driver.
Oracle Direct Connection You cannot use an Oracle Direct Connection in a cross-connection query.
Using a Teradata connection in To use a Teradata connection in a cross-connection query, you must install a
a cross-connection query Teradata ODBC driver.
Using a MySQL ODBC To use a MySQL ODBC connection in a cross-connection query, you must
connection in a cross- specify a database for the connection.
connection query

Toad Data Point 6.4 User Guide

41
Query
 Consideration/Limitation Description
Result sets You cannot edit result sets.
Oracle Long Data Type Cross-connection query does not support Oracle long data types or Oracle
LOBs.

Reverse Engineer Queries

One way to build a query is to begin adding columns and creating joins in the Query Builder, and then send the
query to the editor to add more complex functionality, such as a UNION join. You can also send a query from the
editor to the Query Builder. For example, you inherited a large query when an employee left and you need to edit it
to resolve an issue. After looking at the query in the editor for some time, you still cannot decipher it. So, you right-
click the statement and select Send to Query Builder. Now that you can visually see how the tables are
referenced, you can identify and resolve the issue.

Considerations and Limitations in the Query Builder

Consider the following when reverse engineering a query using the Query Builder:

General Description
Single statement support The Query Builder only supports one statement at a time. If you add multiple
statements to the Query tab, only the first statement is used. If you make any
changes to the statement on the Diagram tab, the other statements are lost.
Quote identifiers If you selected the Quote Identifiers checkbox in Tools | Options
| Database | General and generate a query from the Query Builder in SQL
Server, the query cannot be reverse engineered.
ODBC support You must use ANSI SQL for the query.
Excel support Copying a generated query and attempting to reverse engineer that query
results in an error. This issue occurs because the parser defaults to Oracle
syntax, which does not understand quotation marks for fully qualified object
names. To avoid this issue, clear the Use fully qualified object names and
Use fully qualified column names checkboxes in Tools | Options
| Database | Query Builder or manually edit the query.
Non-ANSI joins (Oracle, SQL When sending a query from the Editor to the Query Builder, Toad
Server, and Teradata) automatically uses ANSI joins. If you use Where clause joins, click to

disable ANSI joins. This converts the statement to the correct join.
Where Condition Description
Where condition does not If you send a query that contains a Where condition from the editor to the
display in the Criteria grid Query Builder, it displays in a Global Where clause bubble on the Diagram
after sending it to the Query pane instead of in the Where Condition field in the Criteria grid.
Builder from the Editor You can double-click the Global Where Clause bubble in the Diagram pane or

can click to edit it.

You can also use the Where condition fields in the Criteria grid to add new
Where conditions. Although they display in the grid, they are added to the
Global Where clause in the Query tab.
Where condition created in If you define a Where condition in the Criteria grid and edit any part of the
the Criteria grid of the query in the Query tab, the Where condition is removed from the Criteria grid
Diagram tab disappears and placed in a Global Where Clause bubble.
You can double-click the Global Where Clause bubble in the Diagram pane or

Toad Data Point 6.4 User Guide

42
Query
 can click to edit it.
Having Condition Description
Having condition does not If you send a query that contains a Having condition from the editor to the
display in the Criteria grid Query Builder, it displays in a Global Having clause bubble on the Diagram
after sending it to the Query pane instead of in the Having Condition field in the Criteria grid.
Builder from the Editor You can double-click the Global Having Clause bubble in the Diagram pane or

can click to edit it.

You can also use the Having condition fields in the Criteria grid to add new
Having conditions. Although they display in the grid, they are added to the
Global Where clause in the Query tab.
Having condition created in If you define a Having condition in the Criteria grid and edit any part of the
the Criteria grid of the query in the Query tab, the Having condition is removed from the Criteria grid
Diagram tab disappears and placed in a Global Having Clause bubble.
You can double-click the Global Having Clause bubble in the Diagram pane or

can click to edit it.

Query Builder Diagram Tab Description
Comments and code regions When reverse engineering a statement from the editor or the Query tab and
removed after making switching to the Diagram tab, if the statement contains a block comment or
changes in the Diagram tab code region and you make a change in the Diagram tab, the comment or code
region is removed from the statement.
Same column cannot be used You cannot use the same column more than once in the criteria grid. Even if
in the criteria grid you create a separate alias for the second use of the column, that column is
created as a calculated field
Execute with different query If the query in the Diagram tab is different than the query in the Query tab,
in the Diagram and Query executing the query executes the SQL in the Query tab instead of the Diagram.
tabs
Save with different query in The Query Builder saves both the statement and diagram in the same file. The
the Diagram and Query tabs contents of the statement and diagram can be different without losing
changes.

To reverse engineer a query

1. Right-click a statement in the Editor, and select Send to Query Builder.See "About Editing SQL" on page
59 for more information.
or

Click in the Query Builder to send a statement to the Editor. Skip the remaining steps and continue to edit
the query. See "About Editing SQL" on page 59 for more information.
Troubleshooting: If the query cannot be modeled in the Diagram tab, a message displays and the
statement opens in the Query tab. This usually occurs because the graphical diagram cannot support some
functionality in the statement. You can view an explanation for this in the Output window or by hovering over

the syntax with a red underline ( ) in the Query tab. To continue, correct the error and click to apply the
change and open the Diagram tab.
2. Continue to build your query visually using the diagram.
3. Select the Query tab and make any additional edits. Indicators for an edited statement are as follows:

Inserted lines

Toad Data Point 6.4 User Guide

43
Query
 Modified or removed lines
Modified characters

Use the toolbar in the lower-right corner of the Query tab to modify the color used for each
indicator.

Caution: If you manually enter or update the statement in the Query tab, you must click to model
the query in the Diagram tab. If you do not do this and make additional changes in the Diagram tab, any
changes you made in the Query tab are lost.

4. Click to execute the query.

Tip: If you have multiple statements in the Editor and you want to send only one to the Query Builder, highlight the
statement, then right-click it and select Send to Query Builder.

Tutorial: Build a Subquery

In this tutorial you will create a subquery to use to filter your selection of address IDs to only those addresses from
specific regions.

To build a subquery
1. Connect to the Toad Sample (Access) database in the Navigation Manager.
2. Select Tools | Query Builder | Query Builder.
3. Drag the ADDRESS table to the Diagram pane.
4. Click these column names in the ADDRESS table to add the columns to the query:
l ADDRESS_ID
l REGION_ID
5. To add a subquery:

a. Select the Where field in the REGION_ID column and click . The Where Condition
editor displays.

b. Select the IN operator from the list, and then click Subquery.

Toad Data Point 6.4 User Guide

44
Query
c. A child Query Builder window displays for you to use to build the subquery.
l The child (subquery) Query Builder window includes a Diagram pane, a Criteria pane, and a
Queries pane for navigating back and forth between subqueries and the main query.

d. Drag the REGION table to the Diagram pane and select the REGION_ID column.

e. Select the Where field in the REGION_ID column and click . In the Where Condition editor, select
the In comparison operator, select Constant, and select the values 1 and 2.

Toad Data Point 6.4 User Guide

45
Query
 f. Your subquery window should look something like this:

g. Click to execute the subquery. Select the Results tab to review the result set for
the subquery.
6. Click the Query node in the Queries pane to return to the main query window. A call-out in the main query
Diagram pane indicates that the statement contains a subquery and identifies the column it is on. Hover over
the call-out to view the subquery statement.

7. Click to execute the main query.

Toad Data Point 6.4 User Guide

46
Query
Using Bind Variables
Toad supports the use of bind variables (bind parameters) in SQL statements. When you execute a statement
containing a bind variable, Toad prompts you to enter a value.
Toad also allows you to specify a default value for the variable before the query is executed by using the Variables
option. The default value can be specified by entering the value manually or by using advanced options to configure
a list of specific values.
If bind variables are defined in the query, clicking on the Variables button will bring up a new window
where all the variables present in the SQL script are listed.

If there are no variables in the SQL script, clicking on the Variables button will raise the following
message:

Automation Scripts. To learn about entering variable values for Automation scripts at run time, see the Set
Variable and Send Email activities in Use System Automation Activities.
Publish with Variables. To learn how to use variables when publishing to Intelligence Central, see Variables in
Views/Scripts and Publishing to Intelligence Central.
Toad supports the following bind variable formats:
l Colon (:variable)
Example: SELECT * from employee WHERE user = :user;

Toad Data Point 6.4 User Guide

47
Query
 l At symbol (@variable) in SQL Server connections
Example: SELECT * from employee WHERE user = @user;
l Question mark (?) in applicable connections, such as ODBC
Example: SELECT * from employee WHERE user = ?;
l Dollar sign ($) in PostgreSQL connections
Example: Select * from employee WHERE user = $1;
Toad also supports these variable formats:
l Double ampersand (&&variable) as a literal replacement SQL*Plus format in Oracle connections
Example: SELECT * from employee WHERE user = '&&user';
Notes:
l Avoid using the same symbol to represent a bind variable and statement delimiter in the same SQL
statement.
l
In the Editor, click to enable/disable binding variables in the SQL. When disabled, Toad does not scan
SQL for parameters. This button is enabled (depressed) by default.

To set the default value(s) for the bind variable before the query is run
1. In the Editor, compose a query that contains bind variables.

2. Click on the Variables toolbar icon .

Note: The Bind Variables dialog does not display if the bind variables option is disabled.
3. Enter the bind variable information. Review the following for additional information:

Name Displays the variable name in the form of a clickable link that brings up the Define
variable prompt values options for creating a list of predefined variable values.
Description Enter the description.
Type Select the data type for the variable from the drop-down list.
Default value Specify the default value(s) that will be used every time the query is run by:
n Entering the value(s) for the variable manually
n Or selecting the value(s) from the predefined list by clicking on the button
and choosing the particular value(s)
Note:
o If a variable has been defined with an IN\NOT IN operator, multiple values
can be entered by using the semicolon as a delimiter (each value needs to
be separated by a semicolon).
o The predefined list of values list needs to be configured in the Define
variable prompt values before using this option

Custom list of values for bind variables

Toad enables you to define a list of specific values that can be used for a bind variable in a query that has been
created in the Query Builder or Query Editor.

Toad Data Point 6.4 User Guide

48
Query
TThe selected values from the list can be changed at any point in time (before and during query
execution) in Toad Data Point/Toad Workbook or Toad Intelligence Central (if the objects are
published).his list of predefined values will be saved in the Query Builder or Query Editor file and
transferred as a part of any downstream step available in Toad (Pivot, Transform and Cleanse,
Dimensional View…).

The selected values from the list can be changed at any point in time (before and during query execution) in Toad
Data Point/Toad Workbook or Toad Intelligence Central (if the objects are published).

To create a list of predefined variable values

To access the options for creating a list of predefined variable values, click on the variable name link that will open
the Define variable prompt values window.

Toad Data Point 6.4 User Guide

49
Query
The following options for defining a list of variable values are available:
l Specify value(s)
This option enables you to define a list of variable values manually by entering multiple values
separated by a semicolon.

Toad Data Point 6.4 User Guide

50
Query
 Toad also allows you to load the variable values from a file by clicking on the ellipsis button
(…) and selecting a specific .txt or .csv file.

Note: Each value in the .txt or .csv file needs to be placed in a new line and no other delimiter
should be specified.

l Get values from the column

This option enables you to create a predefined list of values that contains all the values from a
particular column. The column is selected from the drop-down which lists all the columns defined
in the query.

Toad Data Point 6.4 User Guide

51
Query
l Get values from the SQL script
Clicking on the Edit Query link will open a mini editor in the SQL Script Edit window. This
option allows the user to specify a SQL query that will return a result set for a single column
which will be used to populate the list of predefined variable values. After the query is specified,
it needs to be run in the editor in order to populate the predefined values list.

The query can be entered manually in the editor or it can be loaded from a Query Builder (.tsm)
or SQL file (.sql, .tef) by using the Open button.

Toad Data Point 6.4 User Guide

52
Query
 If the specified query returns a result set with more than one column, it will not be valid and the
following message will be displayed (the same message will appear if the query is not run).

Additional Define variable prompt values options

The Define variable prompt values also provides additional options:
l Allow user to select multiple values – This option is selected by default and it enables the use
of multiple values in the specific variable, meaning that multiple values can be selected for a
single variable when using the IN\NOT IN operator.

If this option is deselected, only a single value can be chosen from the list and passed as a variable value.
l Restrict variable value(s) only to list – This option enables the user to restrict the variable values to the
predefined list which is created in the Define variable prompt values options. That means that the values
cannot be entered manually and can only be selected from the predefined list of values.

Predefined Variable Value List

If the predefined list has been configured in the Define variable prompt values, it will be available in the Variables
window within the Default value field, by clicking on the button.

When the button is clicked, it will open the Predefine Values window which contains:
o A Search box (field) that enables the user to search or filter for specific value(s)

Toad Data Point 6.4 User Guide

53
Query
 o The list of values that have been defined in the Define variable prompt values options.
Depending on the settings that have been applied, the values will be displayed next to a
checkbox (with the Allow user to select multiple values selected) that allows for the selection of
multiple values (as shown in the image above) or next to a radio button (with the Allow user to
select multiple values deselected), which allows for the selection of a single value.

o The Refresh button which can be used to refresh the predefined list of values if they are obtained through
Get values from the column or Get values from the SQL script options.

To use a list of predefined variable values in Toad Automation

In Toad Automation, the options for creating a list of predefined variable values are available when
using Set Variable from System Activities as an automation step.

To enable these options, the Prompt during run checkbox needs to be selected. After the checkbox is ticked, the
Define variable prompt values link appears along with a field for selecting the connection that will be used to run the

Toad Data Point 6.4 User Guide

54
Query
query that populates the list of predefined values for the specific variable.

Clicking on the Define variable prompt values link opens a new window that contains options for
defining a list of variable values.

The options and functionality are identical to those that are found in Toad Query Builder, Editor and Workbook with
the exception of the Get values from the column option, which is not available in Toad Automation.

To use a list of predefined variable values in Toad Workbook

To access the options for defining a custom list of variable values in Toad Workbook, you need to click on the
Variables button, which will open the Workbook Variables dialog.

Toad Data Point 6.4 User Guide

55
Query
Options and functionality for creating a list of predefined variable values match the ones found in the Query Builder
and Query Editor.

Workbook Variables defined with the same name

If a variable is defined in a specific Workbook Workflow with a custom list of predefined values, this list of variable
values (and the option which is utilized to populate the list) will be used for any subsequent variables that are
defined with the same name regardless of the Workflow that is currently selected or changes that have been made
in the Workflow order.
For example – a variable is defined as variable1 in Workflow 2 and a predefined list of values has been created by
using the option Get values from the column.
If another variable is defined with the same name (variable1) in Workflow 1, there will be only one variable present
with that particular name in the Workbook Variables dialog, and the values that were initially defined in Workflow 2
will also be used in Workflow 1.

Toad Data Point 6.4 User Guide

56
Query
The Workbook Variables dialog contains information about all the Workflows that are using a certain
variable in the Used in Workflows column.

To set a bind variable during query run time

1. In the Editor, compose and execute a query that contains bind variables. Toad immediately displays the
Bind Variables dialog.
Note: The Bind Variables dialog does not display if the bind variables option is disabled.

Toad Data Point 6.4 User Guide

57
Query
 2. Enter the bind variable information. Review the following for additional information:

Include An option for including\excluding a specific bind variable at run time. The include
option is selected by default.
Name Displays the name of the bind variable that has been defined in the query.
Description Enter the description.
Type Select the data type for the variable from the drop-down list.
Direction Select the direction of variable from the list.
Value Specify the values for the run by:
n Entering the value(s) for the variable manually
n Or selecting the value(s) from the predefined list by clicking on the button
and choosing the particular value(s)
n Or loading the values from a specific .txt or .csv file by clicking on the ellipsis
(…) button and selecting the appropriate file.
Note:
o If a variable has been defined with an IN\NOT IN operator, multiple values
can be entered by using the semicolon as a delimiter (each value needs to
be separated by a semicolon).
o If you selected a direction of OUT or RETURN, this value is ignored.
o If default values have not previously been set in the Variables option, this
field will be populated with values that have been specified in the last run.
o If default values have previously been configured, any changes made will
apply only for the current run.
o If the values for the run are loaded from a .txt or .csv file, each value in the
.txt or .csv file needs to be placed in a new line and no other delimiter should
be specified.

To use a bind variable in the Query Builder

l In the Where, Having, or OR Condition editor, enter the bind variable in place of a constant.

Toad Data Point 6.4 User Guide

58
Query
To store a value for a bind variable in a global variable
Toad allows you to store a value for a bind variable by defining a global variable. Then, when you execute a SQL
statement containing that bind variable, Toad will not prompt you for a value. You can access this feature from the
Project Manager or the Toad Views manager.

1. Click in the Project Manager or Toad Views Manager toolbar.

2. Enter a name and a value for the bind variable, and specify a data type.
Tip: After you store a bind variable, it is available to be used as a variable in Automation. The stored bind variables
appear in the list of variables in the expression editors in various activities.
Note: Defining and using a custom list of values for bind variables is not supported for global variables.

Use Quote Function to Substitute a String at Run Time

In addition to supporting the use of bind variables, Toad also provides a way for you to easily substitute a string,
such as a database name or table name, in your SQL statement at execution time. This method uses the Quote()
function, one of Toad's built-in functions (known as Toad Script) for SQL statements. The Quote function can insert
the value of a bind variable as a literal value before the statement is sent to the server. This is useful if you want to
use bind variables in places where they are not allowed. See About Toad Script for more information.
For example, suppose you want to create the same table in several different databases, but you do not want to re-
write the SQL statement for each database. You can use one statement that includes the Quote() function in place
of the database name.
Create table {{Quote(:dbname)}}.NewTable as select * from dual
When you execute the statement, Toad prompts you to enter a string value in place of {{Quote(:dbname)}}. You then
enter the database name.

To use the Toad Quote function to substitute a string at run time

1. Enter a SQL statement in the Editor.
2. Use the following syntax in place of the string that you want to substitute.
{{Quote(:name)}}

For example:
Create table {{Quote(:name)}} as select * from dual
3. Execute the statement. The Bind Variable dialog opens.
4. Enter a string to use in place of :name and click OK.
5. Toad inserts the string (quoted if necessary) into the SQL statement and executes it.
Tip: See About Toad Script for more built-in functions that can be used in scripts that you execute in Toad.

Related Topics
About Editing SQL

About Editing SQL

The editor combines a powerful SQL and procedure editor into a single interface.
Notes:

Toad Data Point 6.4 User Guide

59
Query
 l See "Editor Shortcut Keys" on page 61 for more information about keyboard shortcuts used in the Editor.
l Toad has implemented a large script mode to improve performance when opening files larger than 2 MB. By
default, when you open a large file, some features such as word wrap, syntax highlighting, and the Script
Map are disabled in the Editor. When a large script loads, a message indicates that you are in large script
mode in the right margin of the Editor. You can cancel large script mode by clearing the option in Tools |
Options | Editor | General.
l To show the Script Map, Script Results or Group Execute panes for the current Editor window select
Editor | Windows | <pane>.

To edit SQL
1. Select Tools | Edit | SQL Editor.
Note: You can query the worksheet from the Editor by entering a dollar sign ($) followed by a cell range to
limit the results similar to the following statement:
Select * from (MyWorksheet$A1:C10)

You can also query the entire worksheet by entering a dollar sign ($) after the worksheet name; however,
this returns empty rows and columns in the worksheet.
2. Review the following to help enter SQL:
l Code completion
l Script Map
l Code regions
l Code snippets
l Toad views
l SQL recall
l Add/remove application code
l View Objects at Cursor
l Macros to record keystrokes

3. Click to format the code.

4. Right-click a block of code in the Editor to see additional actions available in the context menu. For example,
you can turn a line into comment, uppercase or lowercase it, surround with snippet, set numbered
bookmark, collapse or expand nodes, send to Query Builder and etc. You can also right-click an object (e.g.
a table) and manage it as if in the Object Explorer.
5. Hold Alt + left-click and drag to select vertical blocks of text in Editor.

6. Select the block of code in the Editor, and click to validate the syntax.

Note: Toad undoes any changes to the object and data in the database after executing the script (notice that
the Result Sets tab is empty). To modify the object and data, you need to execute the script.
7. Click Fetch n rows to specify the number of rows to initially retrieve when executing the query. You can set
a default value for this option in Tools | Options | Database | General. See General Database Options .

8. Click to execute SQL on the current server or multiple servers at one time. In the Editor toolbar, the
execute SQL buttons are grouped under . The button will retain the function of the last execute action
for the active Editor window. If you switch to another Editor window, the button will change to reflect the last
action for that window.

9. Select the block of code in the Editor and click to create a stored procedure. Stored Procedure creation
dialog automatically recognizes procedure body and declared parameters.
Tips:

Toad Data Point 6.4 User Guide

60
Query
 l Toad automatically saves a backup copy of any modified editor file in the Application Data Directory to avoid
losing your work. When you restart Toad after an unexpected close, Toad checks the timestamp on the
backup file against the original file, and opens the file that has the latest timestamp. The document recovery
option is in Tools | Options | Environment | General.
l You can save the current connection, SQL script, executed results, filters, and group execute
connections/results (if enabled) 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.
l Editor supports floating window mode.
l The script is automatically checked for syntax errors when executing current statement (F9), checking
syntax, changing database, or refreshing the Script Map. The syntax errors are listed in the Script Results
pane in the Messages tab.
l If you copy and paste a SQL statement from one editor window to another, Toad automatically rewrites the
statement to match the syntax of the connection for the second editor window.

Configure Editor Options

You can specify options for the look and behavior of the SQL Editor by using the Toad Options dialog.

To configure Editor options

1. Select Tools | Options.
2. To specify one of the following options, select the corresponding page in the Options dialog.

Option Description Page in Options Dialog

Display the tabs for the current connection only when using Tabbed Select Environment |
windows Interface
Change background color of the Editor Select Editor | General
Use connection color as background color Select Editor | General
Position Editor tab headers vertically Select Environment |
Interface
Set options to display line numbers, line modifications, and general text Select Editor | General
formatting
Specify options to automatically replace typed text with the correct Select Editor | Auto-replace
substitution text
Customize code formatting options including headers and layout Select Editor | Formatter
characteristics

Click here to view a video of this feature.

Editor Shortcut Keys

You can assign individual shortcut keys in Keyboard options. You can also customize Toad to use the shortcut keys
of existing applications such as Toad for Oracle, SQL Navigator, or Query Analyzer in the Configuration Wizard
(Tools | Configuration Wizard).

Caution! If you have some global access keys assigned, make sure that Toad shortcut keys do not coincide
with them. When using such duplicating shortcut keys in Toad, the third party application commands, assigned
globally, will be performed instead of ones assigned by Toad.

The following table describes the default shortcut keys in Toad.

Toad Data Point 6.4 User Guide

61
Query
Frequently Used Functions Description
CTRL+PERIOD Displays the code completion list
F9 Execute current statement
F5 Execute all statements, in order, one at a time
CTRL+MINUS Comments the selected line
CTRL+SHIFT+MINUS Uncomments the selected line
CTRL+J Collapses all code regions
CTRL+M Expands all code regions
ALT+PAUSE Stops script execution
Bookmark Description
CTRL+SHIFT+0-9 Sets a numbered bookmark at the selected line
CTRL+0-9 Goes to the numbered bookmark
Case Description
CTRL+L Converts the selected text to lowercase
CTRL+U Converts the selected text to uppercase

Clipboard Description
CTRL+C Copies the selected text to the clipboard
CTRL+X Cuts the selected text for pasting in the clipboard
CTRL+V Pastes text from the clipboard
Code Completion Description
CTRL+PERIOD Displays code completion list
ENTER Selects an item and close the code completion list
CTRL+SPACE Selects an item without closing the code completion list
CTRL+<right arrow> Expands a node in the code completion list
CTRL+<left arrow> Collapses a node in the code completion list
Comment Description
CTRL+MINUS Comments the selected line
CTRL+SHIFT+MINUS Uncomments the selected line
Cursor Description
HOME Moves the cursor to the beginning of the line
CTRL+HOME Moves the cursor to the beginning of the file
END Moves the cursor to the end of the line
CTRL+END Moves the cursor to the end of the file
Debugger Description
SHIFT+F12 Step from cursor
F7 Step into
SHIFT+F7 Step out
F8 Step over

Toad Data Point 6.4 User Guide

62
Query
 F11 Run to breakpoint
F12 Run to cursor
CTRL+B Set breakpoint
CTRL+W Add watch at cursor
Delete Description
BACKSPACE Deletes the selected text (if text is selected); otherwise, deletes the
previous character
CTRL+BACKSPACE Deletes text to the beginning of the previous word
CTRL+SHIFT+DELETE Deletes the entire script
Find and Replace Description
CTRL+F Finds text
F3 Finds the next occurrence of text
SHIFT+F3 Finds the previous occurrence of text
CTRL+G Goes to line number
CTRL+I Performs an incremental search
CTRL+SHIFT+I Performs a reverse incremental search
Indent Description
TAB Indents the selected line
SHIFT+TAB Outdents the selected line
Macros Description
CTRL+SHIFT+R Starts/Stops recording a macro
CTRL+SHIFT+P Plays the recorded macro
Miscellaneous Description
F4 Invokes objects search
F4+SHIFT Describes the object next to the cursor
F6 Toggles between the Editor and Data Grid
CTRL+R Show/hide Results pane
CTRL+D Sends SQL to Query Builder
SHIFT+F8 Recalls the previous SQL statement
CTRL+á Scrolls up the window.
CTRL+â Scrolls down the window
<auto-replace token>+SPACE Automatically replaces a misspelled word, inserts symbols, and
expands text.
Note: You can set autoreplace in Tools | Options | Editor |
Autoreplace.
CTRL+SPACE Displays the Code Snippets folder browser
<code snippet shortcut Inserts the code snippet template
name>+CTRL+SPACE
CTRL+DELETE Sets the column value for the current cell to Null in the data grid

Toad Data Point 6.4 User Guide

63
Query
View Result Sets
The Result Sets tab displays a data grid for executed SQL statements and scripts that return data.
If you occasionally closed the Results pane, you can restore it by selecting Editor | Windows | Script Results.

Visual Inspection Grid

You can easily review all (or the selected Result sets) in one Result Sets tab. Left-click and hold Shift or Ctrl to select
more than one Result sets of your script. Select All results to review all in one tab.

To enable view of all Result Sets in one Tab

l Check Options | Database | Script results | Enable all results view

Troubleshoot Data
l
If you cannot edit data, click the red icon in the lower left corner of the grid, and then click OK on the
window that displays. You do not need to edit fields in this window.
l By default, auto commit is enabled and any row changes you make are automatically submitted to the
database. You can disable this option in Tools | Options | Environment | Grid.

Tips for Working with Data

To... Do this...
Pin a result set so it is not overwritten by a subsequent query in Click the push pin beside the result set:
the Editor or Query Builder.

Set the value of a cell to null. Press CTRL+DELETE.

Toggle between the Editor and Data Grid. Press F6.
Add column or group summary values Right-click the data and select Show
| Summary Footer Panel. Then, right-click
the Summary Footer Panel below the
column you want to add a summary total to
and select an option.
If you have grouped columns using the
Grouping panel (Show | Group Panel), you
can also add a summary total to grouped
records.
Apply a summary function to the data (Sum, Min, Max, Count, Right-click below the column you want to
Avg). apply the summary function (but above the
Navigation toolbar) and select an option.
Show or hide columns of data. Right-click a column heading in the data grid
and select Column Chooser.

Toad Data Point 6.4 User Guide

64
Query
To... Do this...
Display data in a report, pivot grid, or chart. Right-click the data and select Send To
|<option>.
Compare differences between two data sets. Right-click the data and select Compare To.
Display the data in a grid or card view; grid view is useful for Right-click a record and select Show |Grid
viewing summary information at a glance; card view is useful for View or Card View.
viewing information in greater detail.
View multi-line text in the data grid as one-line text separated by Right-click the data and select Show | Multi-
spaces. line Text as One-line. When the option is
cleared, only the first line of the multi-line text
is shown. To see the full text hover a mouse
over a data grid or expand it.

Toad Data Point 6.4 User Guide

65
Query
Compare Differences in Data
You can compare result sets from the current editor or a different editor in the Database Explorer's Data tab or the
editor's Result Sets without having to save one of the sets to a file.
Notes:
l If you want to synchronize data, use Data Compare instead of the Data Diff Viewer.
l You cannot modify files in the Diff Viewer at this time.

To compare differences in data

1. Right-click data in the Editor's result set or Database Explorer's Data tab and select Compare To |
Set_number.

You can also save a result set and later use it as a historical comparison.
2. Review the differences.
Tips:
l To switch the contents of the left pane to the right pane, and the contents of the right pane to the

left, click .
l You can also open files to compare by clicking beside the drop-down list at the top of each pane.
l You can show or hide tables that contain equal records, different records, etc., at any time using
the toolbar.

Export Data with One Click Export

Use One Click Export to quickly export data to an Excel report. You can also export data and include a linked query
in the file. A linked query lets you create a "live" Excel report that can be refreshed dynamically. Because a linked
query contains the underlying SQL for a report, each time you refresh the report the query executes and retrieves
the latest data. The dynamic nature of linked query reports makes them ideal for distribution to users who need to
see changes to data on demand without waiting for new static reports to be distributed.

Click here to view a video of this feature.

Notes:
l Before you can export to Excel, the Primary Interop Assemblies (PIAs) for your version of Excel must be
installed. Typically, the PIAs are installed automatically when you install Microsoft Office.
l Use the Export wizard if you need to specify column delimiters and advanced field options when
exporting data.

To export data in one click

1. Select the range of data to export or click any row in a data grid to export all data.
2. Right-click the selected range or data grid and select Quick Export.
3. Select an export option. Review the following for additional information:

Icon Option Description

Excel File Export data to an Excel file. The file is automatically saved under a default
name in the default export folder.

Toad Data Point 6.4 User Guide

66
Query
 Icon Option Description
CSV File Export data to a CSV file (comma separated value file). The file is
automatically saved under a default name in the default export folder.
HTML File Export data to an HTML file. The file is automatically saved under a
default name in the default export folder.
Excel Instance Export data to an Excel instance. Data is copied to a clipboard, Excel is
launched, and a worksheet is populated with data. You can save the
worksheet under a name and folder you specify or save it in the default
export folder.
Excel Instance at Export data to an active cell in Excel. First open Excel and select a cell.
active cell Then select the Excel Instance at active cell option.
Excel Pivot Table Export data to an Excel pivot grid report. See the Excel documentation for
information on creating a Pivot Table report with external data.
Excel Pivot Table Export data to an active cell for an Excel Pivot Table report. First open
at active cell Excel and select a cell. Then select the Excel Pivot Table at active cell
option.
Excel Linked Export the underlying query for data to Excel.
Query
Excel Linked Export the underlying query for data to an active cell in Excel. First open
Query at active cell Excel and select a cell. Then select the Excel Linked Query at active
cell option.
Excel Linked Export the underlying query for data to an Excel Pivot Table report. See
Query with pivot the Excel documentation for information on creating a pivot grid report
table with external data.
Excel Linked Export the underlying query for data to an active cell for an Excel Pivot
Query at active cell Table report. First open Excel and select a cell. Then select the Excel
with pivot table Linked Query at active cell with pivot table option.
Excel Column Export data to an Excel chart instance. Data is copied to the clipboard,
Chart Excel is launched, and a chart is created with the data. You can then
modify the chart's design and save the Excel file.

Tip: To refresh data in a linked query report, click in Excel's External Data toolbar. This executes the
underlying query for the report. See "Share Excel Linked Queries" on page 68 for more information about
creating and using linked queries.

Note: If the window containing data does not have the focus when you select a One Click Export option, the
following message displays: "Export did not find any results sets to process."

Tips:
l To specify a default export folder, go to Tools | Options | Environment | Export.
l To always open a Save File dialog when exporting using One Click Export, go to Tools | Options
| Environment | Export and select the option. This allows you to always customize the file name.

Toad Data Point 6.4 User Guide

67
Query
Share Excel Linked Queries
You can use linked queries to create "live" Excel reports that can be refreshed dynamically. Because a linked query
contains the underlying SQL for a report, each time you refresh the report the query is executed and the latest data
is retrieved. The dynamic nature of linked query reports makes them ideal for distribution to users who need to see
changes to data without waiting for scheduled updates or for new reports to be distributed.
Notes:
l Any user who opens an Excel linked query must have access to a user name and password for the queried
database to refresh the data. If a user does not have access to the database, they can only see the original
data in the spreadsheet.
l If the Excel linked query was created from an ODBC connection that uses a Data Source Name (DSN), the
DSN must also exist on the computer where the Excel file is opened to refresh the data.

Scenario
You need to create an Excel linked query so that the Regional Sales Manager, can track
whether the Sales Representatives in his region meet their monthly and quarterly goals.
By providing a linked query, you only have to create the query once, and then the
Regional Sales Manager can refresh the data at any time to view the latest sales
information.

To create an Excel linked query

1. Connect to the database.
2. Create a query in the Query Builder or Editor.
3. Execute the query to display data results.
4. Select the result set and click one of the following options from the toolbar:

5. Save the Excel spreadsheet.

6. Email or upload the spreadsheet to a shared network folder to share it with colleagues.

To refresh data in an Excel linked query

1. Open the spreadsheet with the linked query in Excel. The original data from the query displays.
2. In Excel, select Data | Refresh to update the original data with the current data.
3. If prompted, enter a user name and password to connect to the database and refresh the data.
or
If the following error displays: "[Microsoft][ODBC Driver Manager] Data source name not found and no
default driver specified", you must specify the DSN used by the query on your computer. Complete the
following steps to resolve this issue:
(Excel 2003)

Toad Data Point 6.4 User Guide

68
Query
 a. Click OK.
b. Select the Machine Data Source tab, and then click New.
c. Use the wizard to select a User Data Source and create a DSN that points to the database.
d. Click OK to return to Excel and open an ODBC Driver Connect window.
e. Enter a user name and password to refresh the data.

Publish Data to Intelligence Central

About Publishing Data
You can use Toad Data Point to publish data to a Toad Intelligence Central (TIC) server in order to store and share
data from remote data sources. Intelligence Central is like a federated server where you can store data from a
variety of data sources. See About Toad Intelligence Central for more information.
Note:This feature is available in the Toad Data Point Professional edition only and requires access to a Toad
Intelligence Central server.
For a video demonstration of this feature, please see Publishing to Intelligence Central.

What Data Sources You Can Publish From

You can publish data from any data source that you can connect to using Toad. Data sources that use native
provider connections must also be ODBC-compliant.

What Type of Objects Can Be Published

Data can be published in the form of views, snapshots, and datasets. See Objects in Toad Intelligence Central for
more information about these objects.

How Data is Published From Toad

You can publish data from any of the following: an object in the Object Explorer, a query statement in the Editor or
Query Builder, or the data in a data grid.

Connectivity and Publishing

Toad Data Point uses three types of connectivity: native database provider connectivity, ODBC connectivity, and
Data Services (Business Intelligence and NoSQL) connectivity. However, Toad Intelligence Central uses only
ODBC and Data Services connectivity. If you publish data objects to a TIC server using a native database provider
connection, the connection is converted to ODBC on the TIC server.

Before Publishing
If you are publishing data using a native provider or ODBC connection, the corresponding ODBC driver must
exist on the TIC server. In addition, for native database connections other necessary client files must exist on the
TIC server. To learn how to prepare the TIC server for publishing, see Prepare to Publish Data to Toad
Intelligence Central.

Related Topics
Publish to Toad Intelligence Central

Toad Data Point 6.4 User Guide

69
Query
Prepare to Publish Data to Toad Intelligence Central
Toad Intelligence Central uses the following types of connectivity to make connections to remote data sources:
l Data Services (for Business Intelligence and NoSQL connections)
l ODBC (for native-provider and ODBC connections)
Because Intelligence Central uses ODBC connectivity, when data is published from a native-provider connection,
Toad converts the connection to ODBC (if the database is ODBC-compliant). Before you can successfully publish
data from a native-provider (or ODBC) connection, everything necessary to create the correct corresponding ODBC
connection must be installed on the Intelligence Central server. This includes the corresponding ODBC driver for the
database type, as well as any additional client files required for a correct ODBC connection.
If data is published from an ODBC connection, you must ensure that the DSN name on the Toad Data Point
computer and the Intelligence Central server match.

Note:This feature is available in the Toad Data Point Professional edition only and requires access to a Toad
Intelligence Central server.

Prepare ODBC Connections in Toad Data Point

If You Use ODBC Connectivity - Use System DSNs

If you plan to publish data objects from a remote database connection using ODBC connectivity in Toad Data Point,
the ODBC connection must use a System DSN. This is required for compatibility with Intelligence Central, which
uses only System DSNs.

To specify a System DSN for an ODBC connection

1. Open the ODBC Data Source Administrator by selecting Start Menu | Control Panel, and then select
ODBC or Data Sources (ODBC).
2. Select the System DSN tab and click Add.
Note: You must configure a System DSN because TIC uses only System DSNs.
3. Then select the ODBC driver for the database to which you want to connect.

4. Configure the remaining settings.

Prepare the Intelligence Central Server

In order for Toad Data Point users to successfully publish data to Intelligence Central, the Intelligence Central server
must have the same ODBC driver, DSN name, and necessary client files that exist on the Toad Data Point user’s
computer for each data source. These files are used by Toad Data Point to create the data source connection.
For the data source type you will be using, ensure that the necessary drivers and files are installed on the
Intelligence Central server, and that they are the same as those installed on the Toad Data Point user’s computer.

Install Toad Data Point

Ensure Toad Data Point is installed on the Toad Intelligence Central host computer. If you plan to schedule and run
Automation scripts on Intelligence Central, Toad Data Point must be installed. Additional benefits of having Toad
Data Point installed along-side Intelligence Central include the following:
l The ODBC drivers for MySQL and SAP ASE, IQ, SQL Anywhere, and HANA are included in the Toad Data
Point installation. This will ensure that theses drivers are installed on the Intelligence Central server.
l You can easily use Toad Data Point to help debug any connectivity issues.

Toad Data Point 6.4 User Guide

70
Query
Beginning with Intelligence Central 3.0, when you install using the Toad Intelligence Central Server Installer, Toad
Data Point is automatically installed.
For best results, ensure the version of Toad Data Point on the Intelligence Central host computer is the same (or
later) as that used to create the Automation scripts.

Install Additional ODBC Drivers and Necessary Client Files

In addition to installing Toad Data Point, install these additional files if publishing from any of these data sources.
Publishing from Oracle Data Sources
To prepare the Intelligence Central server to receive published data objects from an Oracle database, please
ensure that the following are installed on the TIC server:
l OCI client or Oracle client—Oracle Instant Client or standard Oracle client.
l Oracle ODBC driver—If using Oracle Instant Client, you must also install the Oracle ODBC driver. If using
the standard Oracle client, the Oracle ODBC driver is included.
l Tnsnames.ora file
Publishing from DB2 Data Sources
To prepare the Intelligence Central server to receive published data objects from a DB2 database, please ensure
that the following are installed on the TIC server:
l DB2 client
l DB2 ODBC driver
l DB2 catalog setup
Publishing from Teradata Data Sources
To prepare the Intelligence Central server to receive published data objects from a Teradata database, please
ensure that the following are installed on the TIC server:
l Teradata .Net Data Provider
l Teradata ODBC driver. You can download a Teradata driver from http://www.teradata.com.

Ensure Correct DSN Name

Publishing from ODBC Connections
If you plan to publish objects from Toad Data Point using ODBC connectivity, the Intelligence Central DSN must
match the DSN of the original connection used to publish the object.
Ensure the following are true about the Toad Data Point and Intelligence Central ODBC connections:
l Both connections must use a System DSN.
l The Intelligence Central ODBC data source must use the same DSN name as the Toad Data Point
connection from which the object was published.

Configure SMTP Transmission for Automation

If you plan to run Toad Data Point Automation scripts that send email, the Intelligence Central server must be
configured to support SMTP email transmission. For more information about preparing the Intelligence Central
server for published Automation scripts, see Prepare to Publish Automation Scripts.

Install Microsoft Excel

If an Automation Script scheduled to execute on the Toad Intelligence Central server includes an instruction to run a
macro in an Excel spreadsheet, then Microsoft Excel must be installed on the Toad Intelligence Central host
computer. For more information about preparing the Intelligence Central server for published Automation scripts,
see Prepare to Publish Automation Scripts.

Toad Data Point 6.4 User Guide

71
Query
Related Topics
Publish to Toad Intelligence Central

Publish to Toad Intelligence Central

Use the Publish to Intelligence Central dialog to publish data, Automation scripts, and Toad files to a Toad
Intelligence Central server. Share data with others by publishing views, snapshots, and datasets. You can also
share Automation scripts and Toad files for others to copy and/or modify.
To publish generic files, such as Excel files, see Publish Generic Files to TIC.
To publish misc resources, such as database connections or URLs, see Publish Other Toad Resources.

For additional information, see the following:

l Prepare to Publish Data to Toad Intelligence Central
l Prepare to Publish Automation Scripts
l About Toad Intelligence Central
For a video demonstration of this feature, please see Publishing to Intelligence Central.
Note: This feature is available in the Toad Data Point Professional edition only and requires access to a Toad
Intelligence Central server.

To Publish to Intelligence Central

1. To publish data to Intelligence Central, use one of the following methods:
l In the Object Explorer, right-click a table or view and select Publish.
l In the Editor, right-click a SQL statement and select Publish.
l In the Query Builder, right-click a SQL statement and select Publish.
l From a data grid, right-click in the data grid and select Send To | Publish Data.
l Select the Publish to Intelligence Central button in the main toolbar. See Select Data to
Transform, Profile, Visualize, View, or Publish for more information.
2. To publish a Toad document, open the document in Toad and click Publish File in the Wizard bar.
3. In the Publish to Intelligence Central dialog, specify publishing options. Review the following for additional
information:

Publishing Page/Options Option Description

Destination Options
Publish to Server Select a Toad Intelligence Central server connection as the publishing
destination.

Click to review the connection properties for the selected TIC

connection.
Note: If publishing a view, snapshot, or dataset, the destination server
must be licensed for Data Connectivity.
Destination Folder Select the folder where you want to store the published object, or
create a new folder.

Toad Data Point 6.4 User Guide

72
Query
Publishing Page/Options Option Description
l To create a new folder, click the drop-down arrow and then click
New Folder or New Secured Folder. The availability of these
actions depends on the parent folder selected and the server
configuration. See Folders in Intelligence Central for more
information.
You must have publish privileges to a secured folder to publish to it.
You must have manage privileges to a secured folder to create a
subfolder.
(Optional) Select a destination database. This feature is provided for
third-party applications that access the underlying Intelligence Central
databases directly. See "Destination Database in Toad Intelligence
Central" on page 76 for more information.
l
To select or create a database, click . Select an existing
database or create a new one.
Object Options
Publish Type Select the type of object you want to publish. If you are publishing a
script or Toad document, the type is automatically selected.
l Data object—See Data Objects in Toad Intelligence Central for
object descriptions.
l Automation script—See Publish Automation Scripts to
Intelligence Central for additional help.
l Toad document—See Publish Toad Documents to Intelligence
Central for additional help.
l Project Manager—See Publish Projects to Intelligence Central
for additional help.
Note: Data originating from an Excel file, Local Storage, or an Access
database can only be published as a dataset. See "Publishing from
Excel or Local Storage" on page 83 for more information.
Name Create a name to display in the Object Explorer. For rules regarding
duplicate names, see Rules for Duplicate Object Names.
Notes: Only alpha and numeric characters and the underscore (_) are
permitted in object names. A name cannot begin with an underscore.
Publish Detail—(Dimensional View as Dataset only) Click the link to
select which Dimensional Views to publish and to specify dataset
names.
Overwrite Existing Object—Select to overwrite an existing object with
the same name. Select this option when re-publishing after editing a
script or Toad document.
Append Data to Existing Object—Select to append data to an
existing dataset.
Description (Optional) Enter an object description. The description displays in the
Information tab of the Database Explorer.
Tags (Optional) Add tags to use for filtering in the Object Explorer. Tags also
display in the Information tab of the Database Explorer.
a. Click Add Tag to add a tag. Then enter a new tag or click the
drop-down arrow to select from existing tags.
b. Press the Tab key to add additional tags.

Toad Data Point 6.4 User Guide

73
Query
Publishing Page/Options Option Description
Sharing Click the link to modify sharing (visibility) options. Review the following
based on the folder to which you are publishing:
To a new or existing non-secured folder—Specify object sharing
options. See Specify Sharing and Manage Privileges for Objects for a
description of sharing options and manage privileges.
To an existing secured folder—If you do not have Manage
privileges, sharing options are disabled. The object inherits the folder's
sharing and manage privileges.
To a new secured folder—The options you specify are applied to the
folder and the object. Select the users to share, manage, and publish.
See Secured Folders for more information.

To save your changes, click Apply or .

To revert to the default settings, click Reset.
Indexing Options Click the link to create an index.
(Dataset and Snapshot only) Note: If you take no action, no columns are indexed.
See Create/Drop Index for a TIC Object for more information.

To save your changes, click Apply or .

To revert to the default settings, click Reset.
Authentication For a Snapshot or View
(View, Snapshot, Script, and Click the link to view or change authentication keys, or to enter a
Toad file only) connection password.
By default, Toad creates an Authentication key for the object and uses
your user name and password to the remote data source (which is
gathered from the connection used to publish the object).
(View only) For a view, you can change the personal Authentication key
to a shared Authentication key. Click the link to open the Authentication
Settings page. Then do the following:
a. Enter the user name and password (of an existing account) to
be used as the shared credentials.
b. Select Share authentication. This permits all TIC users to
access this data source, even those without a personal login to
the remote source.
c. (Salesforce.com only) If you are creating an authentication key
for a Salesforce.com data source using a security token, you
must enter the token. See Create Salesforce Connections for
more information.
Note: For cross-connection queries, you can specify a personal
Authentication key or a shared Authentication key for each data source
separately.
See User Authentication in Intelligence Central for more information.
For an Automation Script or a Toad File
Click the link to view the connection information for all connections in
the script or file, or to enter a connection password.
l If you did not save the connection password in the Connection
Properties dialog, enter the password now.
l If you did save the connection password, Toad automatically
enters it.

Toad Data Point 6.4 User Guide

74
Query
Publishing Page/Options Option Description

To save your changes, click Apply or .

To revert to the default settings, click Reset.
Variables Click Default values to specify a default value for any variable in the
(View and Script only) SQL or Automation script.
Note: If you take no action, Toad uses existing (cached) values you
specified in the Bind Variables dialog or in the script. If no values exist,
you must specify before publishing.
See "Variables in Views/Scripts and Publishing to Intelligence Central"
on page 78 for more information.
Refresh Data Click the link to schedule the initial snapshot creation and the refresh
(Snapshot only) frequency.
Note: If you take no action, the snapshot is populated immediately.
l Create Initial Snapshot—Specify when to initially populate the
snapshot with data.
l Schedule Recurring Refresh—Specify a frequency at which
Toad will automatically refresh the snapshot.
See Snapshots in TIC for more information.

To save your changes, click Apply or .

To revert to the default settings, click Reset.
Scheduling Click the link to schedule script execution or to run the script
(Script only) immediately.
Test run the script after it is published—Select to run the script
once to test the database connections and the links to input files.
Note: It is recommended that you test run your script when publishing
to uncover any issues such as missing input files or connections that
are not properly set up on the TIC server.
See Publish Automation Scripts to Intelligence Central for more
information.

To save your changes, click Apply or .

To revert to the default settings, click Reset.
Run Details Click the link to specify a user account to run the script.
(Script only) l Specify user to execute automation script—Select to
specify a Windows user account.
l Use default user to execute automation script—Select to
use the default account—the account under which the Toad
Intelligence Central App Server is currently running.
See Publish Automation Scripts to Intelligence Central for more
information.
Preview SQL Click the link to preview the SQL statement.
Send email notification to Select to send a notification email to each user you specified on the
shared users Sharing settings page. After you publish the object, the email is
generated and opens for you to review, edit, and send.
See View Your Intelligence Central Notifications for more information
about all types of notifications.

Important: To undo changes, click Reset. Then click to return to the Summary page.

Toad Data Point 6.4 User Guide

75
Query
 4. Click Publish to publish the object.
The dialog closes and the publishing process runs in the background.
5. After the object publishes successfully, a confirmation window displays and includes a link to the newly-
published object. Click the link to open the object in the Database Explorer (Viewer).
Note: In the event of an error during publishing, use the error message and the troubleshooting section to
troubleshoot, or follow the instructions displayed in the error message.
l See "Troubleshoot Publishing to Toad Intelligence Central" on page 83 for more information.
l See Troubleshoot Scripts on Intelligence Central for more information.

Rules for Duplicate Object Names

Views, snapshots, and datasets: No two data objects can have the same name within the same folder, regardless
of object type: view, snapshot, or dataset.

Toad documents: The name must be unique within a folder per Toad document type. For example, no two ER
Diagrams in the same folder can have the same name, but an ER Diagram and a Query Builder file in the same
folder can have the same name.
Automation scripts: A script name must be unique among scripts. No two scripts can have the same name within
an Intelligence Central instance.

Compatibility with Intelligence Central Versions

l Publishing Automation scripts is supported in Toad Intelligence Central 2.2 or later.
l Publishing Toad documents and generic files is supported in Toad Intelligence Central 2.3 or later.
Tips:
l To publish without executing a query, right-click inside the Editor window or Query Builder Query pane
containing the query and select Publish.
l You can automate the task of publishing data to Intelligence Central as a dataset by using the Execute Script
activity in Automation. See "Use Database Automation Activities" on page 110 for more information.
l You can automate the task of refreshing snapshots in Intelligence Central by using the Refresh Snapshot
activity in Automation. See "Use Database Automation Activities" on page 110 for more information.

Related Topics
Prepare to Publish Data to Toad Intelligence Central
User Authentication in Intelligence Central
Explore Toad Intelligence Central
Publish Other Toad Resources

Destination Database in Toad Intelligence Central

Note: This feature is available in the Toad Data Point Professional edition only and requires access to a Toad
Intelligence Central server.
When publishing an object to Toad Intelligence Central, the Publishing dialog allows you to specify a destination
database. This feature is provided for Toad Data Point users who provision data objects to those who can access
Intelligence Central through a third party application, such as Qliktech or Tableau.
This feature allows the provisioner to store and organize objects into databases. Databases are used by the third-
party-application user to locate and access stored objects.

Toad Data Point 6.4 User Guide

76
Query
Folders in Intelligence Central
The Publishing dialog allows you to select a folder or create a new folder when publishing an object to Toad
Intelligence Central.
l Toad Data Point—Folders are visible to Toad Data Point users through the Object Explorer and are used
by the Toad Data Point user to store, locate, and access objects.
l Third Party Application—The Intelligence Central folders are not visible to the Third Party application.

Databases in Intelligence Central

The publishing dialog allows you to select a database or create a new database when publishing an object.
l Toad Data Point—Databases are visible to Toad Data Point users through the Object Explorer. However,
folders are the primary method used for organizing objects for Toad Data Point users. This is because
folders are more-flexible and easily-customizable through the Object Explorer.
l Third Party Application— Databases are visible to a Third Party application through a MySQL connection
to Intelligence Central and are used to store, locate, and access objects. Databases are the primary method
of organizing objects since the Intelligence Central folders are not visible to the Third Party application.

Third-Party Connection to Intelligence Central

You can create a MySQL connection to Intelligence Central through a third-party application like Qliktech or
Tableau. To learn how, see Third Party Connection To Toad Intelligence Central in the Toad Intelligence Central
Quick Start Guide.
Tip: In Toad Data Point you can create a MySQL connection to Intelligence Central. The MySQL connection allows
you to view the underlying MySQL databases in Intelligence Central. You might want to use this type of connection
to view databases prior to provisioning.

Creating and Specifying Databases

When publishing an object to Intelligence Central, you can specify or create a database in which to store the object
for users who will access it through a third-party application. Organizing objects into databases is important for
these users because Intelligence Central folders are not visible to Third Party applications. See "Publish to Toad
Intelligence Central" on page 72 for more information about publishing objects.

To select an existing destination database

1. In the Publish to Intelligence Central dialog, click next to the Destination Folder field.

2. Select an existing database.

l By default, the destination database is the one associated with the selected folder. If you are creating
a new destination folder, the database name defaults to the new folder name.
l When no destination folder is selected, published_objects is the default database.
Note: If you select an existing folder in the Publishing dialog without specifying an existing or new database,
Toad stores the object in the database with the same name as the folder.

To create a new Intelligence Central database

1. In the Publish to Intelligence Central dialog, do one of the following:
l
If you selected an existing folder in the Destination Folder field, then click and enter a name for
the new database.

Toad Data Point 6.4 User Guide

77
Query
 l
If you created a new folder, you can use the default database name or click and enter a new
name for the database.
Note: If you create a new folder (or sub-folder) through the Publishing dialog without specifying an existing
or new database, Toad creates a default database using the folder name. For a sub-folder, the database
name is <parent folder>_<sub-folder>.

Viewing Databases
Third Party Application
When using a MySQL connection to Intelligence Central, the following databases which contain published objects
are visible:
l User-defined databases—A database is created for each new destination database created through the
Publish to Intelligence Central dialog.
l folder name database—Each time a new folder (or sub-folder) is created through the Publishing dialog
without specifying an existing database, a default database is created using the folder name. This database
contains each object originally assigned to that folder. Even if an object is moved to another folder through
the Object Explorer, the object remains in the original database. For a sub-folder, the database name is
<parent folder>_<sub-folder>.
l published_objects database—The "published_objects" database contains the objects in the "No folder
assigned" folder.
l Legacy databases—If the Intelligence Central instance contains objects that were published using Toad
Data Point 3.6 or earlier, a database was created for each data source (containing all objects published from
that data source). See Explore Toad Intelligence Central for more information about datasource names. In
addition, a database was created for each user and contains their published datasets (data_username).
Toad Data Point
In the Object Explorer, the Intelligence Central databases listed above are visible, as well as the following:
l database/folder name database—If you select an existing folder but do not use the default database and
instead select a different database in the Publishing dialog when publishing an object, Toad creates a folder
under the Databases node in the Object Explorer using the format database/folder.

Rules for Duplicate Object Names

Views, snapshots, and datasets: No two data objects can have the same name within the same folder, regardless
of object type: view, snapshot, or dataset.
Toad documents: The name must be unique within a folder per Toad document type. For example, no two ER
Diagrams in the same folder can have the same name, but an ER Diagram and a Query Builder file in the same
folder can have the same name.
Automation scripts: A script name must be unique among scripts. No two scripts can have the same name within
an Intelligence Central instance.

Variables in Views/Scripts and Publishing to

Intelligence Central
Notes:
l This feature is available in the Toad Data Point Professional edition only and requires access to a Toad
Intelligence Central server.
l This feature is supported in Intelligence Central 3.3 or later.

Toad Data Point 6.4 User Guide

78
Query
Toad allows you to publish Views and Automation scripts containing variables and then specify a default value for
each variable during the publishing process. After publishing, you can modify the default value. For scripts, you can
also provide a value at run time. The variables can be bind parameters or other types of variables, such as those
supported in Automation scripts.
This feature allows data providers to publish views and Automation scripts with variables so that data consumers
can change the variable values when they run the view or execute the script.
Object owners and users with Manage privileges can modify the default value (views and scripts). Any user with
access to a script can enter a variable value at run time when manually executing. Scheduled scripts use the
default value.
This feature applies to the following objects:
l View. SQL published as a view in which the SQL contains one or more variables (bind parameters or
other variables)
l Automation Script (Set Variable). An Automation script containing a Set Variable activity. To enable this
feature, the Prompt during run option must be selected in the activity. See Set Variable activity in Use
System Automation Activities for more information.
l Automation Script (Send Email). An Automation script containing a Send Email activity. To enable this
feature, the Prompt option must be selected in the activity. See Send Email activity in Use System
Automation Activities for more information.
A Note About Datasets: Toad allows you to publish data as a dataset from SQL containing a variable. However,
the cached value for the variable is used and you cannot edit this value after publishing. A cached value must exist
before publishing. Execute the SQL to create a cached value.

Specify Default Value During Publishing

To specify a default value when publishing a view or script
1. When publishing one of the objects listed above, in the publishing dialog click Default values in the
Variables field. See Publish to Toad Intelligence Central.
l If you previously specified variable values, either through the Bind Variables dialog or through the
script, Toad finds and enters those values.
l If a predefined list of custom values has previously been defined in the Variables (Define variable
prompt values) options, the values from that list will be available for selection.
l If cached values are not found, you must specify values before publishing.
Note: This option is enabled only for certain objects (listed above).
2. On the Bind Variables and Parameters page, enter a value for each variable listed.

Edit Default Value After Publishing

To edit the default value after publishing
1. Connect to the Intelligence Central server.
2. In the Object Explorer, right-click the published view or Automation script and select View Details.
3. Select the Variables tab. The default value is displayed.

4. Click to enter a new default value.

l For a view, the new value will be used when the result set is returned.
l For a script, the new value is used when the scheduled script is executed.

Toad Data Point 6.4 User Guide

79
Query
 Tip: In the Object Explorer, select a view or script and click to edit the default value.

Prompt for Value at Run Time (Scripts)

When manually executing an enabled Automation script in Intelligence Central, Toad prompts the user for a variable
value. Enabled scripts were created with the Prompt or Prompt during run option selected in the applicable
Automation activity. See list of applicable objects above. Scheduled script execution uses the default value.

To enter variable values at run time

1. Connect to the Intelligence Central server.

2. In the Object Explorer, select a script and click to run it. Toad prompts you for a variable value. Enter the
value. This value is used for this execution only. The default value remains unchanged.

Considerations and Limitations

Consideration/Limitation Description
ODBC connections: Publishing view You cannot publish SQL containing a variable from an ODBC
with variable is not supported connection to Intelligence Central as a view.
Workaround: You can publish SQL containing a variable from an
ODBC connection to Intelligence Central as a dataset. The cached
value for the variable is used and you cannot edit this value after
publishing.
When inner view is located in another When publishing a view in which the source data is a view from another
TIC instance Intelligence Central instance, the variables in the inner (source) view
are not included in the list of editable variables. For the inner view
variables, the current default value is used.
Using published view in cross- When using a view (containing a variable) from Intelligence Central in a
connection query cross-connection query, you are not prompted for a variable value. The
current default value is used.

Tip: The Web Console in Toad Intelligence Central 3.3 (or later) allows the user to enter a variable value at run time.
In Toad Intelligence Central 4.3 (or later) the user can also modify a variable default value. See the Toad
Intelligence Central Quick Start Guide for more information.

Related Topics
Manage Automation Scripts in Intelligence Central
Views in Intelligence Central

Considerations and Limitations when Publishing to

Intelligence Central
Review the following considerations and limitations for publishing data to Toad Intelligence Central (TIC).

Consideration/Limitation Description
Toad replaces native provider If you publish data using a native database provider connection, Toad
connection with ODBC converts that native connection to an ODBC connection on TIC. In order to

Toad Data Point 6.4 User Guide

80
Query
Consideration/Limitation Description
connection successfully publish from a database, you must have an ODBC driver for
that database installed. Some ODBC drivers are included with your Toad
installation and some (such as Teradata) are not. To see a list of included
ODBC drivers and how to install those you need, see Prepare to Publish
Data to Toad Intelligence Central.
ODBC drivers on Toad If you publish using an ODBC connection, the same ODBC drivers that Toad
Intelligence Central must match used when publishing your data object must also be installed on the Toad
publisher's Intelligence Central server. This is required so that those same drivers can
be used by others when they access your data objects via the server. See
"Prepare to Publish Data to Toad Intelligence Central" on page 70 for more
information.
DSN on TIC must match If you publish using an ODBC connection, the same DSN that Toad used
publisher's when publishing your data object must also be installed on the Toad
Intelligence Central server.
Use System DSNs If you publish using an ODBC connection, when using the ODBC Data
Source Administrator to select a DSN for an ODBC driver, you must select
a System DSN. Toad Intelligence Central runs as a system service and
therefore has access only to the System DSNs.
Specify ODBC drivers for a You can specify the ODBC drivers to use when publishing to an Intelligence
specific TIC connection Central instance. You can do this for each Intelligence Central connection.
To specify ODBC drivers:
1. Right-click the Intelligence Central connection and select Properties.
2. On the Advanced tab, specify the ODBC drivers to use.
Note: Specify an ODBC driver to use only if you are having difficulty
publishing from that particular database to the TIC instance.
Publishing from an Oracle When publishing from an Oracle native provider connection, Toad is using
connection an OCI client, an ODBC driver, and the tnsnames.ora file. All three of these
components must exist on the client computer and on the TIC server to
successfully publish an object from an Oracle connection to TIC.
The Oracle alias names in the tnsnames.ora file on the TIC server must
match those in the client's tnsnames.ora file.
If you encounter an error when publishing from Oracle, one of these
components may be contributing to an incorrect connection. Please see
Troubleshoot Publishing to Toad Intelligence Central for help resolve Oracle
issues that involve these components.
Unsupported Oracle timestamp Advanced timestamp data types, such as TIMESTAMP_WITH_LOCAL_
data types TIME_ZONE and TIMESTAMP_WITH_TIME_ZONE, are not supported in
published objects.
Oracle TIMESTAMP is supported.
Publishing from a DB2 connection When publishing from a DB2 native provider connection, Toad is using a
DB2 client, an ODBC driver, and the DB2 catalog. All three of these
components must exist on the client computer and on the TIC server to
successfully publish an object from a DB2 connection to TIC.
Publishing from Excel or from an You can publish data to Intelligence Central from these sources, but only as
Access or Local Storage a dataset, not as a snapshot or view. See "Publishing from Excel or Local
database Storage" on page 83 for more information.
Publishing from a localhost You cannot publish data (as a view or snapshot) from a local database (or
connection Intelligence Central) connection defined as localhost.
This restriction also applies when publishing Toad documents containing a
connection and Automation scripts containing a connection.

Toad Data Point 6.4 User Guide

81
Query
 Consideration/Limitation Description
Workaround: Define the connection using the data source IP address or
network name instead of localhost.
Publishing from a SQL Server You cannot publish from a SQL Server connection that uses Windows
connection using Windows Authentication. In addition, you cannot publish from a SQL Server
Authentication connection if you select a publishing key that uses Windows Authentication.
Intelligence Central cannot authenticate the SQL Server Windows
Authentication account for a user who is not currently logged in on the
Intelligence Central server.
Publishing from a SQL Server To successfully publish from a SQL Server connection that uses SQL Server
connection using SQL Server Authentication, the SQL Server user account must have db_datareader
Authentication privileges and the view permission.
Publishing from a connection You cannot publish from a database connection that uses Windows
using Windows Authentication Authentication. For example, you cannot publish from an Oracle connection
or an SAP HANA connection that uses Windows Authentication. To publish
successfully, publish from a connection that uses database user name and
password.
Publishing a cross-connection If you publish a view or a snapshot from a cross-connection query, it is
query - use unique aliases strongly recommended that you ensure that all aliases are unique within the
entire SQL.
WITH clause limitations To successfully publish a view or snapshot from SQL containing a WITH
clause, ensure the SQL adheres to the following:
l Use only a standard name when naming the common table
expression
l A common table expression cannot have an alias
l The entire SQL can contain only one WITH clause
l A common table expression cannot be defined using a statement
that contains the name of another common table expression
Note: Here the term common table expression also refers to subquery
factoring (Oracle) or any other type of named subquery block.
BLOBs are not supported When publishing an object that contains a binary large object (BLOB) data
type column, the BLOB data type column will not be included in the
published object.
Hive data sources, changing If you create a Hive data source from a non-SSH Hive connection, you
authentication key type cannot change the authentication key to an SSH-type authentication key or
vice versa.
To change the authentication key to SSH (or vice versa), you must recreate
the data source.
FULL OUTER JOIN not supported Intelligence Central does not support full outer joins at this time. Use UNION
instead.

Related Topics
Publish to Toad Intelligence Central
Troubleshoot Publishing to Toad Intelligence Central

Toad Data Point 6.4 User Guide

82
Query
Publishing from Excel or Local Storage
Data originating from an Excel file, Local Storage, or an Access database can be published to Toad Intelligence
Central (TIC), but only as a dataset because these data sources are unmappable.

To publish from an unmappable data source to Intelligence Central

1. Launch the Publish to Intelligence Central dialog. See "Publish to Toad Intelligence Central" on page 72 for
more information.
2. In the Publish Type field, Dataset is the only option available.
3. Specify the remaining publishing options.
4. When finished, click Publish to publish the data as a new dataset in TIC.

Publishing a Cross-Connection Query Containing an Unmappable

Data Source
You can publish a cross-connection query that includes a connection to an Excel file, Local Storage, or an Access
database. The data from the cross-connection query is published as a data object in TIC. In addition, the data from
the Excel file, Local Storage, or Access database is published separately to TIC as a new dataset, and is used by
the main published object.
Data in the Dataset Is Static
The main object in TIC does not contain a link to the original Excel file, Local Storage database, or Access
database. If data changes in one of these original sources, the change is not reflected in either the main object in
TIC or in the dataset in TIC.
Dataset Sharing/Visibility
The same sharing options you specify for the main object are applied to the dataset.
Note: If all connections in the cross-connection query are from Excel, Local Storage, or Access, then the data is
published as a dataset only.

Related Topics
Publish to Toad Intelligence Central
Datasets
About Data Sources

Troubleshoot Publishing to Toad Intelligence Central

Use this section to troubleshoot publishing errors.
To troubleshoot issues when publishing an Automation script, see Troubleshooting Automation Script Publishing.

Issue or Error Message Solution or Workaround

Google Analytics or Salesforce - Error Msg: "Invalid data source definition: 400 Bad Request: Invalid_
expired access/refresh token grant (expired access/refresh token)"
This error may occur if the connection's authentication token is expired.
Solution: In the Publishing dialog, click the Authentication link, then
click Reauthorize to re-enter your credentials and grant access to Toad.

Toad Data Point 6.4 User Guide

83
Query
Issue or Error Message Solution or Workaround
"Data source name not found" error Error Msg: "Invalid data source definition: Data source name not found
msg when using ODBC connectivity and no default driver specified"
Solution 1: TIC uses only System DSNs. Make sure that you have
specified a System DSN and not a User DSN.
Solution 2: The DSN on the TIC server must match the DSN you are
using. Ensure that the DSN installed on the TIC server is the same as
the one you are using to publish.
Note: This only applies when the connection you are publishing from
uses ODBC connectivity.
missing MySQL ODBC driver on TIC You are missing the MySQL ODBC driver. You can download the driver
from the following site: http://dev.mysql.com/downloads.
Use MySQL ODBC driver versions 3.51 or 5.0.
missing Teradata ODBC driver on TIC You are missing the Teradata ODBC driver. You can download the
driver from the following site: http://www.teradata.com.
In addition to the Teradata ODBC driver, you must also install the
Teradata .Net Provider.
missing driver on TIC The Intelligence Central server you are publishing to is missing the
matching ODBC driver. Install the required ODBC driver listed in the
error message, or contact the administrator responsible for the
Intelligence Central server.
You might also want to specify an ODBC driver to use when publishing
from this database type to this particular TIC server.
To specify an ODBC driver
1. In the Navigation Manager, right-click the Intelligence Central
connection and select Properties.
2. Select the Advanced tab and then select an ODBC driver (from
the drop-down list) to use for that database type.
Note: Specify an ODBC driver to use only if you are having difficulty
publishing from that particular database to the TIC instance.
General problems publishing from an General issue: The Microsoft-supplied ODBC driver for Oracle is
Oracle connection included in a standard Windows install and has limited functionality. To
insure that the TIC server is using the Oracle ODBC driver instead of the
Microsoft-supplied driver, you can specify a driver to use when
publishing to that TIC instance.
Suggestion: Specify driver to use when publishing.
1. Right-click the TIC connection in the Navigation Manager and
select Properties.
2. Select the Advanced tab. Open the drop-down list next to Oracle
and select the Oracle ODBC driver.
ORA: 1242 General issue: The TIC TNS names file may have a data service name
that does not match the name in the TNS names file on the publishing
workstation.
Suggestion: Modify one of the files to ensure the names match.
"The ODBC driver does not support The Oracle ODBC driver does not support subtracting from
the data type." TIMESTAMP. This is a known Oracle ODBC issue. If attempting to
publish SQL such as the following:
Select Date_Col, Timestamp_Col - sysdate from Table
remove "Timestamp_Col - sysdate."

Toad Data Point 6.4 User Guide

84
Query
 Issue or Error Message Solution or Workaround
"Out of sort memory" error Error Msg: "Out of sort memory, consider increasing server sort buffer
size."
This error might be encountered when attempting to publish a query or
when querying a published snapshot/view containing a GROUP BY or
ORDER BY clause.
Solution 1: Add “ORDER BY NULL” to the end of the query.
Solution 2: Increase the value of the sort_buffer_size variable by
adding "sort_buffer_size=2M" to the Intelligence Central configuration
file found here:
C:\Program Files\Quest Software\Toad Intelligence
Central\datahub.cnf
Columns with the same name Error Msg: "Duplicate column name '<column name>'. Add a
differentiating column alias."
Your query includes two columns with the same name and the columns
do not have unique aliases.
Solution: Add unique alias names for the columns.
"Incorrect key file" error msg Error Msg: "Incorrect key file for table 'C:\WINDOWS\TEMP\<myi file
name>'; try to repair it."
Solution: The server is low on disk space. Increase available disk
space.
Specified key was too long Error Msg: "Specified key was too long; max key length is 1000 bytes"
This error may occur if you selected columns to index on the Indexing
Options tab (applies to snapshots and datasets only). There is a
limitation in the TIC database: The maximum index size (key length) is
1000 bytes.

Related Topics
Troubleshooting Automation Script Publishing
Publish to Toad Intelligence Central

Toad Data Point 6.4 User Guide

85
Query
 5

Report
Tutorial: Create a Toad Data Report
Use the Data Report Designer to design reports visually. Stored in .tdr files, data reports are "live" reports that can
be refreshed dynamically. What makes them dynamic is that they contain the underlying queries for report data
sets. Each time a data report is opened, its query runs and the latest data is retrieved. The dynamic nature of data
reports makes them ideal for distribution to users who need to see changes to data without waiting for scheduled
updates or for new reports to be distributed.

The process of creating a data report has the following steps:

l Step 1: Run the Toad Data Report Wizard

l Step 2: Update Fields

l Step 3: Use Styles to Format the Report Appearance

l Step 4: Update Bands and Add Controls

Note: This procedure does not cover all of the possible steps of creating a data report. It only covers the steps
required to create a report for the scenario. See About Designing Toad Data Reports for more information.

Scenario
Your company wants to improve its global sales, starting with countries where it has a customer base but aver-
ages low sales. You created a query that displays countries with less than $500,000 in sales per fiscal year,
but now you need to create a report to help represent and evaluate the results. The data should be grouped
per country and show the total and average sales for each country. In addition, you want the report to look
similar to the rest of the company's documents, which use specific colors and fonts.
Note: This scenario uses the data generated in Visually Build a Query.

Step 1: Run the Toad Data Report Wizard

The Toad Data Report wizard creates an initial layout of the report based on options you select, including what
columns to include, whether to group data, and the basic style.

To run the wizard

1. Right-click the data grid in the Results tab and select Send To | Data Report Designer. The Toad Report
Designer displays.

2. Select Standard Report and click Next.

3. Click to include all columns in the report and click Next.

4. Select COUNTRY_NAME and click , and then click Next. This groups the data by the country name so that
each country is listed as a heading and its sales per year display below it.

Tip: You can create a secondary group by selecting another column and clicking again. A secondary
group is not appropriate for this scenario, but it would be if the data included regions in each country or
fiscal quarters. You would need to make the region or fiscal quarter a secondary group for the data to be
organized appropriately.

5. Select the Sum and Avg checkboxes for the SUM(SALES.AMOUNT) row, and then click Next. These
options calculate the sum and average sales amount for each country.

6. Select Outline 1 and click Next.

Tip: The Outline and Align Left options are good choices if you grouped the data.

7. Select Casual and click Next.

Toad Data Point 6.4 User Guide

86
Report
 8. Enter Countries with Sales Less Than $500,000 in the Report Title field, and then click Finish. The initial
report layout displays in the Report window with the information grouped under bands.

9. Click Preview to see what the report looks like in print. Previewing the report makes it easier to see how
the bands and their content display in the printable report.

Step 2: Update Fields

You can move, rename, and update the category of a field.

To update the fields

1. Complete the following steps to update the field category:

a. Select the FISCAL_YEAR field in detailBand1.

b. Click directly above the field.

c. Click by the Format String field.

d. Clear the Prefix field and click OK.

2. Double-click the following fields and enter their new name:

Field Band Name

FISCAL YEAR groupHeaderBand2 Fiscal Year

SUM(SALES.AMOUNT_SOLD) groupHeaderBand2 Sales Amount

Avg groupFooterBand1 Average

3. Select the following fields one at a time and make them wide enough to see the full text: Sum (groupFoot-
erBand1), Average (groupFooterBand1), and Grand Total (reportFooterBand1).

4. Select the following fields one at a time and drag them to the one inch mark on the ruler: Sum, Average,
and Grand Total.

5. Right-click the COUNTRY NAME field (not the COUNTRY_NAME field on the right) in groupHeaderBand1 and
select Delete.

Note: The COUNTRY NAME field is a label that precedes the COUNTRY_NAME field value. If you preview
the report before you delete the COUNTRY NAME field, the country displays as 'COUNTRY NAME Argentina'.
It is clear that Argentina is the country name, so this field is redundant.

6. Select the COUNTRY_NAME field and drag it to align with the report title.

7. Select the COUNTRY_NAME field and make it three inches wide to accommodate long country names.

Step 3: Use Styles to Format the Report Appearance

The Data Report Designer uses styles to format different objects and kinds of data. Once you define a style, you
can apply it to similar fields to create a consistent and easy-to-update design.

To create, update, and apply styles

1. Complete the following steps to update the report title style:

a. Select the Styles field in the Properties pane.

b. Click . The Styles Editor window displays.

c. Select Title.

d. Select the Foreground Color field, and then select MidnightBlue in the Web tab.

Toad Data Point 6.4 User Guide

87
Report
 Note: All colors in this scenario are from the Web tab.

e. Select the Font field and click . The Font window displays.

f. Select 22 in the Size field and click OK.

2. Complete the following steps in the Styles Editor window to create and define four new styles:

a. Click four times in the Styles Editor window to create four new styles.

b. Set the following properties for the new styles:

First Style Properties

Name OddRow

Background Color WhiteSmoke

Border Color MidnightBlue

Borders Bottom

Font Tahoma, 10pt

Second Style Properties

Name EvenRow

Border Color MidnightBlue

Borders Bottom

Font Tahoma, 10pt

Third Style Properties

Name TableHeading

Background Color MidnightBlue

Font Tahoma, 10pt, bold

Foreground Color White

Fourth Style Properties

Name CountryName

Font Tahoma, 14pt, bold

Foreground Color Black

3. Complete the following steps to apply the new styles:

a. Expand the Styles field in the Properties pane.

b. Select TableHeading in the Style field (under Styles) for the Fiscal Year and Sales Amount
fields in groupHeaderBand2.

c. Set the following fields for [FISCAL_YEAR] and [SUM(SALES.AMOUNT_SOLD)] in detailBand1:

Toad Data Point 6.4 User Guide

88
Report
 Field Property

Even Style EvenRow

Odd Style OddRow

d. Select the COUNTRY_NAME field in groupHeaderBand1 and then select CountryName in the Style
field.

Step 4: Update Bands and Add Controls

Bands group the information layout in the report and you can add controls to include additional tables, images,
fields, and other items in the report. For the scenario, you will expand bands, delete a band, and add a control.

1. Right-click pageHeaderBand1 and select Delete.

2. Complete the following steps to add a line under the report title:

a. Select the groupHeaderBand1 and move it down half an inch.

Tip: It is helpful to expand a band before adding controls to give you plenty of room in which to
work.

b. Drag Line from the Toolbox into reportHeaderBand1.

c. Expand it to the width of the page.

d. Drag and position it directly below the report title.

e. Enter 2 in the Line Width field in the Properties pane.

3. Select reportFooterBand1 and drag it down half an inch. This adds white space between the country inform-
ation.

The Data Report Designer displays with the following layout:

4. Click Preview.

Toad Data Point 6.4 User Guide

89
Report
 The print preview displays with the following layout:

5. To save the report, right-click the Data Report Designer tab and select Save File.

Manage Reports
About Managing Reports
Use the Report Manager to create and organize data reports, add existing Toad or Excel reports, or generate
reports.

To create a report
1. Select Tools | Reports | Report Manager from the menu.

2. Right-click a folder and select Create Report.

3. Complete the wizard and design the report.

4. Save the report.

5. To add the report to the Reports Manager window:

a. Navigate to the open Reports Manager window.

b. Right-click the folder where you want to add the report, and select Add Existing Report.

To generate an existing report

Toad Data Point 6.4 User Guide

90
Report
 1. Select Tools | Reports | Report Manager from the menu.

2. Double-click the existing report.

3. Review the following for additional information:

Direction Select the direction of variable from the list.

Value Enter the value of the variable.

Note: Enter a percent (%) in this field to generate all values.

Tips:

l To add an existing Toad or Excel report to the Report Manager, right-click the Report Manager window and
select Add Existing Report.

l To send a Toad or Excel report via email or to a shared folder or other location, right-click the report, select
Send To, and then select an option.

l To share reports with other users, specify a shared network directory in Network Share Options (Tools |
Options | Environment | Network Share).

DB2 Reports
The following reports are available from the Reports Manager (Tools | Reports | Reports Manager).

Report Name Description

Activity Lists and describes activity reports that the Activity Monitor for the current DB2 for LUW
database uses. This report also lists the switches required to gather data for a specific
activity report. Toad uses the AM_BASE_RPTS table function to generate this report.

ADMIN_LIST_HIST_ Displays information from the database history table for the DB2 for LUW database par-
V82 tition to which Toad is currently connected. Toad uses the ADMIN_LIST_HIST table func-
tion to generate this report.

Database Partitions Shows information about each database partition on the current DB2 for LUW database.
Toad uses the DB_PARTITIONS function to gather this information from db2nodes.cfg.

DB Manager Config Lists the database manager configuration parameters and their values defined for the
Info current DB2 for LUW database.

HEALTH_CONT_HI Shows the current "health" status of all containers that tablespaces in the current DB2
for LUW database use. Toad uses the HEALTH_CONT_HI table function to generate this
report.

HEALTH_CONT_HI_ Shows a "health" history for all containers that the tablespaces in the current DB2 for
HIS LUW database use. Toad uses the HEALTH_CONT_HI_HIS table function to generate this
report.

HEALTH_CONT_ Identifies the containers that tablespaces in the current DB2 for LUW database use and
INFO lists the highest current alert status for each container. Toad uses the HEALTH_CONT_
INFO table function to generate this report.

HEALTH_DB_HI Shows the "health" status of the current DB2 for LUW database, including its highest cur-
rent alert status. Toad uses the HEALTH_DB_HI table function to generate this report.

HEALTH_DB_HI_HIS Shows the "health" history of the current DB2 for LUW database. Toad uses the
HEALTH_DB_HI_HIS table function to generate this report.

HEALTH_DB_HIC Shows the "health" status of objects in the current DB2 for LUW database. Toad uses the
HEALTH_DB_HIC table function to generate this report.s

HEALTH_DB_HIC_ Displays a "health" history for objects in the current DB2 for LUW database. Toad uses
HIS the HEALTH_DB_HIC_HIS table function to generate this report.

HEALTH_DB_INFO Shows the highest current alert status for the current DB2 for LUW database. Toad uses
the HEALTH_DB_INFO table function to generate this report.

HEALTH_DBM_HI Shows the "health" status for the current DB2 for LUW instance. Toad uses the HEALTH_

Toad Data Point 6.4 User Guide

91
Report
Report Name Description

DBM_HI table function to generate this report.

HEALTH_DBM_HI_ Shows a "health" history at the database manager level for the current DB2 for LUW
HIS instance. Toad uses the HEALTH_DBM_HI_HIS table function to generate this report.

HEALTH_DBM_INFO Shows the highest current alert status for the current DB2 for LUW instance. Toad uses
the HEALTH_DBM_INFO table function to generate this report.

HEALTH_TBS_HI Shows the "health" status of all tablespaces in the current DB2 for LUW database. Toad
uses the HEALTH_TBS_HI table function to generate this report.

HEALTH_TBS_HI_ Shows a "health" history for tablespace in the current DB2 for LUW database. Toad uses
HIS the HEALTH_TBS_HI_HIS table function to generate this report.

HEALTH_TBS_INFO Shows the highest current alert status for each tablespace in the current DB2 for LUW
database. Toad uses the HEALTH_TBS_INFO table function to generate this report.

Index Statistics Displays statistics for the indexes under the current schema for the DB2 for LUW data-
base and flags those indexes that need reorganization. Toad uses the REORGCHK_IX_
STATS procedure to generate this report.

Installed DB2 Lists the DB2 for LUW 9 products currently installed on your system. Toad uses the ENV_
Products GET_PROD_INFO table function to generate this report. Products can include the fol-
lowing: RTCL DB2 Run-Time Client CONSV DB2 Connect Server (any edition) CONPE
DB2 Connect Personal Edition

Installed DB2 Lists the DB2 for LUW 8 products currently installed on your system. Toad uses the ENV_
Products_V82 GET_PROD_INFO table function to generate this report. Products can include the fol-
lowing:
l ADCL DB2—Application Development Client

l ADMCL DB2—Administration Client

l CONEE DB2—Connect Enterprise Edition, DB2 Connect Unlimited Edition, or DB2

Connect Application Server Edition

l CONPE DB2—Connect Personal Edition

l CUBE DB2—Cube Views

l DLM DB2—Data Links Manager

l ESE DB2—Enterprise Server Edition

l EXP DB2—Express Edition or DB2 Express Edition processor option

l GSE DB2—Spatial extender

l PE DB2—Personal Edition

l QP DB2—Query patroller

l RTCL DB2—Run time client

l WM DB2—Warehouse Manager

l WSUE DB2—Workgroup Server Unlimited Edition

Instance Inform- Lists details about the current DB2 for LUW instance. Toad uses the ENV_GET_INST_
ation_V82 INFO table function to generate this report.

Recommendations Provides recommendations (in English) for the various activity reports that the activity
for Activity Reports monitor uses. Toad uses the AM_BASE_RPT_RECOMS table function to generate this
report.

SNAP_GET_ Displays information and statistics about each container that tablespaces in the current
CONTAINER _V82 DB2 for LUW 8 database use. To generate this report, Toad uses the SNAP_GET_
CONTAINER table function to retrieve information from the tablespace_container logical
data group.

SNAP_GET_DB_V82 Lists information and statistics captured for the current DB2 for LUW 8 database. To gen-

Toad Data Point 6.4 User Guide

92
Report
 Report Name Description

erate this report, Toad uses the SNAP_GET_DB table function to retrieve information
from the database and detail_log logical data groups.

SNAP_GET_DYN_ Lists statistics and information about the dynamic SQL statements executed against the
SQL_V82 current DB2 for LUW 8 database use. To generate this report, Toad uses the SNAP_GET_
DYN_SQL table function to retrieve information from the dynsql logical data group.

SNAP_GET_TAB_ Lists information and statistics about the tables in the current DB2 for LUW database 8.
V82 To generate the report, Toad uses the SNAP_GET_TAB table function to retrieve inform-
ation from the table logical data group.

SNAP_GET_TBSP_ Lists information and statistics for the database partitions that make up each tablespace
PART_V82 in the current DB2 for LUW 8 database. To generate this report, use the SNAP_GET_
TBSP_PART table function to retrieve information from the tablespace_nodeinfo logical
data group.

SNAP_GET_TBSP_ Lists information and statistics for each tablespace in the current DB2 for LUW 8 data-
V82 base. To generate the report, Toad uses the SNAP_GET_TBSP table function to retrieve
information from the tablespace logical data group.

SNAPSHOT_AGENT_ Lists the agents working for the various applications connected to the current DB2 for
V82 LUW 8 database. To generate the report, Toad uses the SNAPSHOT_AGENT function to
retrieve information from the application snapshot, especially from the agent logical
group.

System Inform- Displays information about the operating system in which the current DB2 for LUW 8
ation_V82 database operates and about the CPUs configured on this operating system. Toad uses
the ENV_GET_SYS_INFO table function to generate this report.

Table Statistics Displays statistics for the tables under the current schema in the DB2 for LUW database
Column View and flags those tables that need reorganization. Toad uses the REORGCHK_TB_STATS
procedure to generate this report.

Table Statistics Displays statistics for the tables under the current schema in the DB2 for LUW database
and flags those tables that need reorganization. Toad uses the REORGCHK_TB_STATS
procedure to generate this report.

MySQL Reports
The following reports are available from the Reports Manager (Tools | Reports | Reports Manager).

Report Name Description

Help Topic Displays the first 10 rows of the mysql.help_topic table.

ODBC Reports
The following reports are available for the Toad Sample Database in the Reports Manager (Tools | Reports
| Reports Manager):

Report Name Description

Customer orders Displays customer orders for contact IDs less than 10 from the Toad sample database. It
is an example of master detail display using a single result set.

Customer list Displays a list of customers from the Toad sample database.

Items to order Displays items in stock that have a quantity of less than 50 and should be reordered from
the Toad sample database.

Oracle Reports
The following reports are available from the Reports Manager (Tools | Reports | Reports Manager).

Cluster Reports

Toad Data Point 6.4 User Guide

93
Report
 Report Name Description

Clusters Displays cluster information, including the cluster type, definitions, storage parameters,
and affected tables.

Other Reports

Report Name Description

Invalid objects Displays any invalid objects for the selected schema and includes a subtotal of all invalid
objects for that owner.

Loads into shared Displays packages that have been loaded five or more times, into the Oracle shared pool.
pool Note: You must have DBA privileges for this report.

Non-system objects Displays any objects in the SYSTEM tablespace that are not owned by SYSTEM.
in SYSTEM tables

Oracle initialization Displays the values for all parameters defined in the PFILE for the selected user.
parameters

Tablespaces near Displays tablespaces that over 90% full, and do not have the autoextend feature
capacity enabled.

Total shared pool Displays the number of times objects in the shared pool were reloaded.
reload stats Note: You must have DBA privileges for this report.

User hit ratios Displays the buffer cache hit ratio users and includes consistent gets, blocked gets, and
physical reads.

User resource usage Displays values for resource usage such as CPU used and SQL*Net roundtrips for each
user.

Sequence reports

Report Name Description

Sequences Displays the sequences that are within 10% of the maximum value. Any sequences that
do not have a maximum value set, or those that cycle are excluded from the report.

Sequences near Displays the sequences that are within 10% of the maximum value. Any sequences that
max do not have a maximum value set, or those that cycle are excluded from the report.

Stored Program Reports

Report Name Description

Arguments of Displays any parameters required for the selected stored procedure.
stand-alone stored
programs

Object type bodies Displays the body or source code for a selected object.

Object types Displays the type of objects owned by a user, including whether the objects are valid and
when they were created or last modified.

Package bodies Displays the body or source code for a selected package

Package spe- Displays specifications for a selected package, including whether it is valid and when it
cifications was created or last modified.

Stand-alone stored Displays the source code for a stored program, including the type of program and the last
programs time DDL was generated.

Stored functions Displays a list of stored functions for a selected owner.

Stored object with Displays a list of all stored programs including their source.
source

Stored procedures Displays the source for stored procedures for a selected owner.

Toad Data Point 6.4 User Guide

94
Report
Synonym Reports

Report Name Description

Synonyms Displays information for each synonym, including the owner, table owner and name, and
the DB link.

Table Reports

Report Name Description

Indexes Displays details for the index definition, storage parameters, and columns included in the
index.

Non-indexed tables Displays any tables that have not been indexed for the selected database.

Table comments Displays any comments for the selected table.

Tables & columns Displays all columns for a selected table, including the maximum transactions, used/free
blocks, free lists and groups, min/max extents, and columns included in this index.

Tables & indexes Displays any indexes for a selected table, including the status, uniqueness, and columns
included in the index.

Trigger Reports

Report Name Description

Triggers & columns Displays triggers for a selected table, including the trigger event, status, WHEN clause,
and columns associated with the trigger.

User-defined Reports

Report Name Description

Sample Displays a sample user report based on a select * from dual query.

View Reports

Report Name Description

View comments Displays any comments for the selected view.

Views & columns Displays all columns for a selected view, including the query and columns included in the
view.

SQL Server Reports

The following reports are available from the Reports Manager (Tools | Reports | Reports Manager).

DBA Reports

Report Name Description

All jobs Displays all scheduled SQL Server jobs and the job owner, organized by server.

Backup history Displays the backup start date, type, and device used for the backup for a specified data-
base for a set number of days.

Backup status Displays the database name, date of the last backup, days since the last backup, and the
type of backup performed.

Current lock report Displays any locks on the server, including the database name, object or index name,
type of lock, resource, mode, and status.

Toad Data Point 6.4 User Guide

95
Report
 Report Name Description

Database con- Displays configuration options for each database on the specified server.
figuration options

Database settings Displays any common options and their values for all the databases on your server.

Database user Displays the user name, roles, objects, granted/denied status, and column permissions
details for each database.

Display errors Displays a log file without entries for log backups. This is useful when you have a number
of log backup entries and you need to locate non-backup errors.

Drive space usage Displays space for all databases on the server, including free space, space used, max-
imum size, and growth type.

Foreign key Displays tables in the order you can either populate or delete from them without violating
sequence any foreign key constraints. Objects with the lowest wave number indicate that they can
be deleted with the fewest number of foreign key references. Objects with the highest
wave number should be populated first.

Index description (SQL Server 2000 only) Displays all indexes for the current database sorted by table.
Index information includes whether it is clustered, unique, index keys, and the index
size. run frequency, and the maximum duration.

Job details (SQL Server 2000 only) Displays all SQL Server jobs, including whether they are sched-
uled, enabled, start date and time,

Row count for tables Displays the number of rows for all tables on a server that contain a clustered index.
with clustered index

Total rows and Displays the total number of rows and space used for each table or view in the current
space used database, including the reserved rows, unused rows, and index size. This report is sorted
in descending order by reserved rows.

User permissions Displays users permissions, grouped by role name and object type.

Other Reports (SQL Server 2005 only)

Report Name Description

Configuration change his- Displays a history of all sp_configure and Trace Flag Changes recorded by the
tory default trace.

Schema change history Displays a history of all committed DDL statement executions recorded by the
default trace.

Table Reports

Report Name Description

Indexes Displays index information for a selected table, including whether the index is
clustered, unique, ignores duplicate keys, allows page/row locks, and a list of
indexed columns.

Non-indexed tables Displays any tables that have not been indexed for the selected database.

Table constraints Displays any check constraints for a selected table, including whether the con-
straint is enabled, valid data, and affected columns.

Table extended prop- Displays any extended properties for a selected table.
erties

Table relationships Displays any foreign key constraints for a selected table, including whether the con-
straint is enabled, referenced tables, and referenced keys.

Tables and columns Displays all columns for a selected table, including the data type, default value,
whether it allows null values, and for SQL Server 2005 any extended properties.

Tables and indexes Displays any indexes for a selected table, including the name, columns included in
the index, and whether it is unique or clustered.

Toad Data Point 6.4 User Guide

96
Report
User-defined Reports

Report Name Description

Stored procedures Displays stored procedures for a selected database, including their status, status,
replication information, schema version, statistics, and procedure text.

Publish and Share Reports

Toad provides multiple ways to publish and share information with your business community. Use the following
methods to publish and share information from the Project Manager, Reports Manager, and Automation.

l Email reports and files, including Database Diagrams, Query Builder queries, SQL scripts, and results for
reports, queries, and scripts.

l Share reports and files in a central location such as the Toad shared folder.

l Add reports and files to a ZIP folder or file.

l Publish reports and files to Toad Intelligence Central

You can use the following methods to publish and share information:

To email from... Do this...

Automation Drag the Send Email activity to the Automation workflow and attach files as
needed. The email is sent when the automation script executes.

To publish to a central loc- Do this...

ation from...

Project Manager, Right-click a report or file and select Send To | Toad Shared. The file is
Reports Manager sent to the Toad shared folder specified in Tools | Options | Environment
| Network Share.

Automation Drag the Copy File activity to the Automation workflow. Specify the file to
copy or move and a destination folder. This can be any folder, including the
Toad shared folder, or a network folder. The file is copied or moved when the
automation script executes.

To send to a zip file... Do this...

Automation Drag the Zip File activity to the workflow. Add reports and files to the Zip file
and specify a file name with a .zip extension. The ZIP file can then be emailed
with the Send Email activity or copied with the Copy File activity.

To publish to Intelligence Do this...

Central from...

Project Manager, Right-click a file and select Send To | Publish File.

Reports Manager

Automation Drag the Publish Files activity to the Automation workflow. Add reports and
files created previously in the script or add other existing files.

Tips:

l To export a Toad report to a file that can be read by other applications, double-click a report in the Reports

Manager, click in the Report Preview window, select a file format, and add the file to a project. Share
or publish the file using the Project Manager functions.

l To capture report results in a file, add the Toad Report automation activity. This activity runs a Toad report
and saves the results in a file format you select. Share or publish the file using the Automation functions.

l To execute and save a query in a file, add the Select To File automation activity. This activity runs a query
or SQL script and exports the results to an Excel spreadsheet. Share or publish the spreadsheet using the
Automation functions.

Toad Data Point 6.4 User Guide

97
Report
 6
Automate

Getting Started with Automation

Summary

This tutorial helps you get started with Automation by giving you an overview of the Automation module and then
guiding you through the process of creating a basic script.
In this tutorial you will learn:
l How to use the Automation window
l How to create a basic script using the Select to File activity
l How to test and run a script
l How to schedule a script
l How to create a template for reusing an Automation activity

Watch a video version of this tutorial: Getting Started with Automation.

Note: The Automation module is disabled if Toad was installed with the Prohibit saving passwords
option selected.

Introduction
Toad provides an Automation utility that allows you to easily script database activities and schedule them using the
windows scheduler. In this way, database tasks can be automated to save you time. Toad Automation can increase
your productivity by allowing you to automate tasks that you perform repeatedly.
The Benefits of Using Automation
l Automating repeatable tasks allows you to be more productive and increases your efficiency.
l Toad Automation scripts are very flexible and extensible, allowing you to customized them to your needs.
l You can create and use variables in Automation scripts. Variables allow you to make your scripts flexible
and to customize your scripts to different audience and company needs.
l You can incorporate a variety of templates into Automation scripts. Using templates in your scripts can help
you get the most out of automating your tasks. Templates include: Microsoft Excel® files, Export Wizard
templates, or Toad Report templates. You can even create an Automation activity template allowing you to
reuse an activity.
l There is no limit to the number of tasks you can include in an automation script.
l Toad Automation scripts are conveniently scheduled and run by the Windows scheduler. You can track and
manage your scheduled scripts using Toad or the Windows Scheduler interface.

Toad Data Point 6.4 User Guide

98
Automate
* Did You Know *

Opening the Automation Window

There are several ways to open an Automation window.
l To build a script from scratch, use one of the following methods to open an Automation window:
l Select Tools | Automation.
l Click the Automate button in the main toolbar.
l Click Automation in the Launch window.
l To build a script incorporating your current workflow, use the following steps:

1. In the Query Builder or Editor, build or enter your SQL statement.

2. Click Send to Automation in the Wizard bar.

Toad sends your SQL to the Automation Tutorial (Guided Tour) which opens in a separate
window.
3. Select a task and complete the wizard.
4. After you click Finish in the wizard, an Automation window opens containing your new script.
The script incorporates your SQL statement and the settings you specified in the wizard.

The Automation Window

1. To get started with Automation, open an Automation window by selecting Tools | Automation.
The Automation window consists of a Toolbox pane (along the left side of the window), a design pane (top
half of the window), and a details pane (bottom half of the window).

Toad Data Point 6.4 User Guide

99
Automate
 Toolbox—Stores the Automation activities. Click an activity in the Toolbox to add it to the script design
pane. Activities are listed by category: Database, File, or System.
Script Design Pane—Graphically displays the script as a workflow. Use the script design pane to build an
Automation script. Drag an activity to the design pane to add the activity to a script.

Note: You must drag the activity to an Add Activity icon in the Automation workflow. When you see the
activity's icon displayed in the workflow, release the mouse button.
Details Pane—Displays an activity’s settings. Click an activity in the design pane to display its configurable
settings in the details pane. Use the details pane to specify the settings for each activity in the script.
2. In the Toolbox, find the Database Activities group. Single-click the Select to File activity to add it to the
script design pane.
3. With the Select to File activity still selected in the script design pane, view the details pane. The details pane
allows you to configure settings for the selected activity. The input fields change depending on which activity
is currently selected in the design pane.
Note: The Activity Input tab is the tab where you will specify most of an activity's settings.

4. Click next to the Select to File activity. Toad uses this icon to indicate that more information is required to
complete the activity. Click or hover over the icon to display the missing information. Use this icon to help
you determine which input fields are required.
Note: This icon is also used to indicate a validation error, for example, when referencing a variable that has
not been created yet.
5. Now click the Settings icon in the design pane. The details pane changes to display input fields for the
script’s settings. You can use this area to specify script error handling, logging level, and run environment

Toad Data Point 6.4 User Guide

100
Automate
 settings.

6. Click the Stop on Error button, if not already selected. This instructs the script to stop if an error is
encountered.
Note: You can also enable or disable the Stop on Error option for each individual activity. If you know that a
particular activity will have an error, you can disable the Stop on Error option for just that activity. This
option is located on the Activity Info tab for each activity.

7. In addition to selecting the Stop on Error option, enabling the Send email when error occurs and Attach
log file to email options is also preferred. This can be very useful if your script encounters an error.
Enabling your script to send an email notification on error, as well as on success, helps you to feel confident
that your processes are running as expected.
If you choose to enable these options, you must specify email settings. To specify email settings, click the
Compose Email button.
8. Keep this Automation script window open, and proceed to the next section.

Create a Basic Script

1. If you kept the previous Automation window open, you will have a basic script consisting of one activity, the
Select to File activity. Click on the Select to File activity in the design pane.
2. In the Activity Input tab, in the first text box, select a database connection, or use the default. The default
database connection for each database activity is always the currently-active open connection. To select a
different database connection, click and browse to a different connection.

Note: If you schedule a script to run against a password-protected database for which you did not save the
password in Toad, the script will error. To save the password, right-click the connection in the
Connection/Navigation Manager and select Properties. Enter the password and select the Save
Password option.
Tip: For Snowflake connections, it is considered best practice to use only the Snowflake user account
authentication when creating Automation scripts to avoid unnecessary workflow interruptions.
3. Now select an input. Most activities require an input, and in the Select to File activity the input is a SQL
statement. You can input a SQL statement using one of the following methods:
l Manually enter a SQL statement in the editor box (deselect the Link SQL file option).
l Click to browse to and select a SQL file.

l Select Link SQL file, then click to browse to and select a SQL file. This option retains a link to
the SQL file.

l
Click to open the SQL file (you previously selected) in the Editor where you can modify the SQL.
l Select Link SQL file, then click the drop-down list to select a file from the currently open project in
the Project Manager.

Toad Data Point 6.4 User Guide

101
Automate
 4. After selecting or entering your SQL statement, select an output. Select Excel in the Type field in the Export
File section.

5. Click in the Name field and select a location and enter a name for your new Excel file.

6. You can also add a suffix to the file name. In the suffix field, click the drop-down to select a datetime stamp,

or click to build an expression. You can use this method to add a variable or a custom datetime stamp.
Appending a datetime stamp to the file name allows you to create a unique file with each script execution.

7. You now have a complete activity, with input, output, and database connection specifications. You can save
and reuse this activity in other scripts by saving the activity as a template. To do this, right-click the activity
(after configuring all the settings) and select Save As Template. Enter a name for your template and click
OK to save it.
Your template now appears in the Toolbox in the Templates category.

8. Keep the Automation script window open and proceed to the next section.

Test and Run Your Script

1. To test your script, click Run in the Wizard bar.
2. Toad will prompt you to save your script (if you have not already done so). In the Save As dialog, select a
location, enter a file name, and click Save.

3. Toad then validates your entries and compiles the script. If these processes are successful, the script runs.
4. While the script is running, Toad displays your script’s execution progress in the Log tab. The message
“Done” indicates script execution is finished.

5. When a script produces an output file, a hyperlink to the output file is included in the Log. If your script ran
successfully, click the output file hyperlink to view the new Excel file created by your script.
6. Click the Settings icon to return to the script’s settings.

Toad Data Point 6.4 User Guide

102
Automate
 7. If you require a more detailed Log, you can change the logging level. In the Settings Activity Input tab, select
Verbose from the Logging level drop-down list to create a more detailed log.
Note: If you want to change the location of the Log file, select a new location in the Logging folder
input field.
8. Toad allows you to specify both a Test database connection and a Production database connection for each
script. Toad also allows you to specify which connection to run your script against when the script executes.
Both of these settings are made in the script’s settings. This feature allows you to easily test your script in a
test environment before running your script in the production environment.
To specify a test and a production database, make the selections in the Connection text box under Test
environment and Production environment in the Settings Activity Input tab. To select which of these
databases to run your script against, select Test or Production from the Select environment drop-down list.
9. Click the Select to File activity to return to its settings. Then select the Activity Info tab.
10. If you remember from the overview of the Automation window, Toad allows you to enable or disable each
activity in a script. This can be helpful when building a script, as it allows you to test one particular activity at
a time by disabling the other activities.
In the Activity Info tab, click Disabled to disable the Select to File activity. Notice that the activity now
appears shaded in the design pane. To enable the activity again, click Enabled, or right-click the activity in
the design pane and select Enabled.

Schedule Your Script

Once you have thoroughly tested your script, you can schedule it.
1. To schedule your script, click Schedule in the Wizard bar.
2. The Job Manager window opens and a task properties dialog displays. The task properties dialog is pre-
populated with the information required to run your script as a scheduled task. You only need to schedule it.
(The Job Manager uses the Windows Scheduler.)

3. To schedule the script, select the Triggers tab. Then select the scheduling trigger and click Edit. In the Edit
Trigger dialog, ensure that the On a schedule trigger type is selected. Then specify a frequency, start time,
interval, etc.
4. Click OK to save your schedule. Click OK to save and close the task properties dialog.
5. Your task (script) is now listed in the Task List in the Job Manager. Double-click your task in the list to open
the task properties dialog again where you can make modifications, such as re-scheduling or changing the
script location. Use the Task List to review the status of your tasks (scheduled scripts).
6. By default, the scheduled task runs under your Windows account. If you change your Windows password,
you may also need to update the password in your scheduled task (depending on your version of Windows).
Click and select the task to update. Click OK and then enter your password when prompted.

7. After upgrading to a new version of Toad, you must migrate the scheduling tasks to the newer version. See
"Upgrade Automation Scripts and Scheduling Tasks" in the online help for more information.

Toad Data Point 6.4 User Guide

103
Automate
 8. Script Execution and Status. When Toad is in the process of executing a scheduled script, you will see the
Toad icon in the lower right corner of the Windows taskbar. To check the status of your scheduled scripts,
open the Job Manager (Tools | Administer | Job Manager) and select the Task List.
Now you have learned how to increase your productivity by using Toad Automation to handle your repetitive
reporting and deployment tasks!

Automate Exporting to Excel Reports

Summary
This tutorial walks you through the process of automating the task of exporting query results to simple Excel reports.
You will create three different, and very useful, simple Excel reports.
In this tutorial you will learn:
l How to use Automation to export query results to Excel
l How to use the Select to File activity in Automation
l How to automate an Excel report that appends a new worksheet with each execution
l How to automate an Excel report that uses multiple queries to produce multiple worksheets

Watch a video version of this tutorial: Automate Exporting Data to Excel.

Introduction
After creating a result set in Toad, you may need to send the results to your end users. The easiest way to
create a report containing query results is to export the data to an Excel file by right-clicking the data grid and
selecting Quick Export | File | Excel File. This action creates a basic Excel spreadsheet containing the result
set from the data grid.
If you want to create more than a basic spreadsheet and automate the process, Toad Automation helps you build a
variety of Excel reports, from simple worksheets to complex and attractive Excel reports.
In this introduction to automating Excel reports, you will learn how to automate the process of exporting data to a
variety of simple Excel reports.

Note: To learn how to get started with Automation, see Getting Started with Automation.

Create a Simple Excel Report

1. Select Tools | Automation.
2. Click the Select to File activity in the Database Activities pane of the Toolbox, or drag the activity from the
Toolbox to the Automation design window.

Toad Data Point 6.4 User Guide

104
Automate
 3. When the Select to File activity is selected in the design window, the Activity Input tab displays. Select a
database connection in the first text box or use the default (the default is the currently-active open
connection).
4. Then select a SQL file or enter a SQL statement using one of the following methods:
l Manually enter a SQL statement in the editor (deselect the Link SQL file option).
l Click to browse to and select a SQL file.

l Select Link SQL file, then click to browse to and select a SQL file.

l
Click to open the SQL file (you previously selected) in an editor where you can modify the SQL.
l Select Link SQL file, then click the drop-down list to select a file from the currently open project in
the Project Manager.

5. Select Excel in the Type field in the Export to File section.

6. Click in the Name field to select a location and enter a name for your new Excel file, or you can select an
existing file.
Note: To specify more-detailed options for the Excel file, click the Export options button.
7. (Optional) You can choose to create a unique file by adding a suffix. In the suffix field, click the drop-down to

select a datetime stamp, or click to build an expression.

8. Select Overwrite if you want to overwrite the file.

Note: If you did not add a suffix, you can deselect Overwrite to append a new worksheet to the Excel file
with each script execution.
9. To save your Automation script, click Save in the Wizard bar. Then select a location and file name for
your script.
10. To test your script, click Run in the Wizard bar.
11. After the script executes, the Log tab displays. Click the hyperlink in the Log to view the Excel file.
12. To learn how to schedule your Automation script, see Schedule Your Script.

Toad Data Point 6.4 User Guide

105
Automate
That was simple, wasn't it. Now move on to the next example to learn how to create a slightly more complex
Excel report.

Create a Multi-Page Excel Report Using One Query

Suppose you want to create a new report each week from the same query while retaining the previous week’s
results. In this case, you want to append each week’s results as a new worksheet to a single Excel file. This is an
excellent option if you want to compare the results each week (or each day, month, etc.) to see how the data is
changing. (And Toad has several data compare features in the application to let you do that.)
1. Select Tools | Automation to open a new Automation window.
2. Click the Select to File activity in the Toolbox to add it to the Automation design window.
3. Select a database connection.
4. Select a SQL file or enter a SQL statement using one of the methods described in the first example.

5. Select Excel in the Type field in the Export to File section.

6. In the Name field, click to select a location and enter a name for your file.

7. Leave the suffix field blank (adding a date/time suffix could create a new file with each script execution and
this is not the objective of this example).
8. Deselect Overwrite (you do not want to overwrite the file).
9. Now click Export options. The Export options dialog opens.
10. In the Worksheet name field, enter a name to be used as the base worksheet name.
11. Select Append timestamp to named worksheet. This will append the date and time (that the data was
exported) to the base worksheet name. Click OK to save your settings and close the dialog.

12. Click Run to save and run your script.

13. Open the Excel file by clicking the link in the Log tab. Notice that the new worksheet’s name includes the
base name you specified suffixed with the timestamp.
14. Run the script a second time and open the file. A new worksheet with a new timestamp was added to the
Excel file.
The output of this script is one Excel file containing multiple worksheets. Each worksheet contains a result set from
one execution of the script. And each worksheet name records the date and time the result set was exported.

Create a Multi-Page Excel Report Using Multiple

Queries
Another variation of the multi-page Excel report is to export the results of multiple queries. Each worksheet in the
Excel file will be populated with the result set of a different query. This can be accomplished by using a SQL file that
contains multiple SQL statements as the input file.

Toad Data Point 6.4 User Guide

106
Automate
 1. To start, open a new Automation window.
2. Click the Select to File activity to add it to the design window.
3. Select a connection.
4. Select a SQL file or enter a SQL statement using one of the methods described in the first example.
However, this time use a file that contains multiple SQL statements (or enter multiple statements in
the editor).
5. Select Excel in the Type field in the Export to File section.

6. Click in the Name field to select a location and enter a name for your new Excel file.

7. Click Export options and make sure the Worksheet name field is blank.
Note: If the Worksheet name field is not blank, your script will export both result sets into the named sheet,
and the second result set will overwrite the first.

8. Selecting to overwrite the existing file or to add a date/time suffix to the file name is optional in this example.
9. Click Run to save and run your script.
10. Open the new file by clicking the link in the Log tab. Notice that the file contains a separate worksheet for the
result set of each query.
The output of this script is one Excel file containing multiple worksheets. Each worksheet contains the result set of a
different query.

Schedule A Script
See Schedule Your Script to learn how to schedule the Automation script.

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" on page 127 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:
l Automation scripts are compatible with the version of Toad in which they are created.
l The Automation module is disabled if Toad was installed with the Prohibit saving passwords
option selected.

Toad Data Point 6.4 User Guide

107
Automate
To automate tasks
1. Select Tools | Automation.
2. Click Settings (icon) in the script design window and specify script settings. Enter settings in the bottom
pane. Review the following for additional information:

Toad Data Point 6.4 User Guide

108
Automate
Activity Input
Tab
Stop or continue Select Stop On Error to stop the script if an error occurs.
on Error
Send email Select this option to send an email on script error.
when error If selected, you must also click Compose Email to compose the email message and
occurs 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 Select to convert all local file paths to relative paths in all activities in the script
path to find files (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.
Note: Toad deselects this option when publishing a script to Intelligence Central.
Embed files into Embeds Toad input files into the script. This makes it easier to share the script with
Automation colleagues.
script 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, Data Cleansing, Visualization, 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.
Note: Toad selects this option when publishing a script to Intelligence Central.
Truncate log Select to overwrite the log file each time the script runs.
Selected Use this area to specify a "test" database connection and a "production" database
environment connection and then easily switch between the two.
l Test—Select to run your script in test mode using the database connection
and other settings you specified under Test environment.
l 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
l 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.
l 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.

Toad Data Point 6.4 User Guide

109
Automate
 Activity Info Tab
Name Enter a script name.

3. 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 .
4. 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:
l Use Database Automation Activities
l Use File Automation Activities
l Use System Automation Activities
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.
5. When you finish building your script, select one of the following actions from the Automation toolbar or the
Wizard bar:

Add to Project 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.
Save

Run 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:
l Press F5 to run the automation script.
l To view the script log, select the Log tab or select View | Output.
Publish Publish script to Toad Intelligence Central.

Schedule 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" on page 131 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:
l Double-click an activity in the script design window to open the Activity Properties pane in a format used in
earlier versions of Toad.
l Right-click within the script design window and select Print Script to print the script's workflow diagram.

Use Database Automation Activities

You can use the following activities in automation scripts.

Toad Data Point 6.4 User Guide

110
Automate
Note: When building an automation script, you must have the appropriate connections open for database activities.
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.

Activity Description
Select to File 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.
l Click the drop-down list to select a file from the currently open project in the
Project Manager.
l Click to browse to and select a SQL file.

l
Click to open the SQL file in the Editor where you can modify the SQL
file.
l 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" on page 127 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. To use a
variable as a suffix, enter the hash symbol before and after the variable name
(#VariableName#).
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. Export Options—Click to specify more export options.

l Worksheet name—Enter a worksheet name. For an existing file, select the
worksheet from the list. Overwrite must be deselected to display the list.
To add a worksheet to an existing file, select Append worksheet from the
list and enter a new name.
l Append rows—Select to append to the selected worksheet.
l 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 Script Execute an existing SQL script, Query Builder file, or Editor file. You can save the results
of the script in a variable or export the results to Local Storage or an Intelligence Central
dataset.
1. Select a database connection.
2. Select a SQL file or enter a SQL statement.

Toad Data Point 6.4 User Guide

111
Automate
Activity Description
l Click the drop-down list to select a file from the currently open project in the
Project Manager.
l Click to browse to and select a SQL file.

l
Click to open the SQL file in the Editor where you can modify the SQL
file.
l 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" on page 122 for more information.

4. Local Storage—To export the results to Local Storage, select a database and a
new or existing table (or snapshot).
5. Intelligence Central—To export the results to Intelligence Central as a dataset,
click Publishing Wizard and specify your publishing options.
Review the following for additional information:
l 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" on page 127 for more information.
l Overwrite existing dataset—Select to overwrite an existing dataset with the same
name.
Select to Editor with Execute an existing SQL script or Query Builder query and save it and any results sets in a
Results 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.
l Click the drop-down list to select a file from the currently open project in the
Project Manager.
l Click to browse to and select a SQL file.

l
Click to open the SQL file in the Editor where you can modify the SQL
file.
l 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:
l 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

Toad Data Point 6.4 User Guide

112
Automate
Activity Description
activity. See "Using Variables in Automation" on page 127 for more information.
l 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 Wizard Export data from databases, tables, views, or a query into a CSV, Excel, or other file.
Review the following for additional information:
l 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#).
l 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.
l 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" on page 127 for more information.
Import Wizard Import data from a CSV, Excel, or other file into a new table or append to an existing table.
You can also import data from one database to another.
Review the following for additional information:
l 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#).
l 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.
l 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" on page 127 for more information.
Toad Report 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:
l Select a connection to execute the Toad Data Report query against.
l 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.
l Edit Report—Click to edit the selected report.
l 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" on page 127 for more information.
l 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

Toad Data Point 6.4 User Guide

113
Automate
Activity Description
the variable name (#VariableName#).
l 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.
Note: In most cases, the script uses the connection you specify in this activity. However,
when a Toad Data Report contains a cross-connection query, the cross-database
connection in the report takes precedence.
For more information about Toad Data Reports:
l Watch Automation and Toad Data Reports (video).
Toad Pivot Grid Open an existing Toad Pivot Grid file, execute the query, and export the data to Excel or
other file format, Local Storage, or Intelligence Central.
Note: You must create a Toad Pivot Grid file (.tpg) before you can select it using this
activity.
Review the following for additional information:
1. Select a connection to execute the Toad Pivot Grid file against.
2. Select an existing Toad Pivot Grid file. You can browse for a file or select from the
currently-open project. Click to edit the selected pivot file.

3. Export to file—Select a file type and enter a file name 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#).
l To export to an Excel pivot table, select Excel Pivot.
l Click Export Options to specify Excel file options.
4. 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.
5. 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" on page 127 for more information.
6. Export to—Select one of the following:
l Local Storage—To export pivoted data to Local Storage, select a database
and a new or existing table.
l Intelligence Central—To export pivoted data to Intelligence Central as a
dataset, click Publishing Wizard and specify your publishing options.
Profile Data Automate data profiling tasks. This activity profiles data from a selected query and exports
a report containing the profiling results.
1. Select a database connection.
2. Select a SQL file or enter a SQL statement.
l Click the drop-down list to select a file from the currently open project in the
Project Manager.
l Click to browse to and select a SQL file.

l
Click to edit the selected file.
l Enter a SQL statement in the editor.

Toad Data Point 6.4 User Guide

114
Automate
Activity Description
3. To specify options for the profiling task, click Profiling Options.
4. Create Report—(Required) Select a location, select a file format, and enter a file
name for the exported report (PDF, HTML, etc).
5. 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.
6. Export Data Profiling File—(Optional) Select a location and enter a file name for
the Toad Profiling Report file (.tpr).
Note: You cannot attach this file in subsequent activities in the script.
See Data Profiling for more information.
Clean Data Automate data transformation and cleansing tasks. This activity opens an existing Toad
Data Cleansing file, performs the transform and cleansing tasks on the current result set,
and exports the modified data to a file, Local Storage, or Intelligence Central.
1. Connection—Select a database connection.
2. Cleaning file—Select a Toad Data Cleansing file or edit an existing file using one of
the following methods:
l Click the drop-down list to select a file from the currently-open project in the
Project Manager.
l Click to browse to and select a file.

l
Click to edit the selected file.

3. Error options—Click to specify error options.

4. Select one of the following actions:
l Export to a file—Select a file, or select a location and enter a new file name.
You can use a variable in this field by entering the hash symbol before and
after the variable name (#VariableName#).
l Local Storage—To export to Local Storage, select or create a database.
Then select or create a snapshot or table to receive the modified data.
l Intelligence Central—To export the results to Intelligence Central, click
Publishing Wizard and specify your publishing options.
Review the following for additional information:
l 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" on page 127 for more information.
l 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.
See Transform and Cleanse Data for more information.
Visualize Data Open an existing Toad Data Visualization file (.tdv), refresh the chart, and export contents
as a report. Select from a number of output file formats, including PDF and Excel.
l Data Visualization document—Select an existing Toad Data Visualization file.
l Output file—Select a file location, file name, and file type for your output file. You
can use a variable in this field by entering the hash symbol before and after the

Toad Data Point 6.4 User Guide

115
Automate
Activity Description
variable name (#VariableName#).
l 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.
See Visualize Data for more information.
Compare Data 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.
l Data Compare Project—Select an existing Toad Data Compare project file.
l Summary Report—Select a file location, file name, and file type for the comparison
summary report file.
l Object reports folder—Select a destination folder for the detailed object
comparison reports generated by the activity.
l 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.
l Generate sync script—Select a location and enter a file name to generate a
synchronization script. Click to configure options for the synchronization
script.
l Execute script—Select to execute the generated script.
l Compare result variable—Enter a variable name or use the default. This variable is
assigned a value of True or False based on the results of the data comparison.
See Create Data Comparisons for more information.
Refresh a Snapshot Use this activity to refresh a snapshot in Local Storage or in Toad Intelligence Central
(TIC). This action will update the snapshot to reflect the current data in the source table.
Select one of the following:
l Local Storage
l Database—Select the database containing the snapshot to refresh.
l Snapshot—Select the snapshot to refresh.
l Intelligence Central
l Select a destination—Select the TIC connection containing the snapshot to
be refreshed.
l Datasource—Select the data source in TIC containing the snapshot to
refresh.
l Snapshot—Select the snapshot to refresh.
Database In previous versions of Toad, the Database Connection activity was required in order to
Connection 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.

Toad Data Point 6.4 User Guide

116
Automate
Activity Description
Loop Connections 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.
l Select connections to loop through—Click Add Connections to select one or
more connections to loop through.
l 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 SAP ASE.
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.

l 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.
Dimensional Viewer Open an existing Dimensional View file, execute the query, and export the data to Excel,
Local Storage, or Intelligence Central.
1. Select an existing Dimensional View file.
2. Export to file—To export data to Excel or .csv, select a file type, a file location, and
a file name. Click Export Options to specify export options. In the Export dialog,
each tab represents a view.
l Worksheet name—Enter a worksheet name. For an existing file, select the
worksheet from the list. Overwrite must be deselected to display the list.
To add a worksheet to an existing file, select Append worksheet from the
list and enter a new name.
l Append rows—Select to append to the selected worksheet.
l Start export at—Enter the column and row position at which to begin the
export.
3. Export to:
l Local Storage—To export data to Local Storage, select a database.
l Intelligence Central—To publish the data to Intelligence Central as a

Toad Data Point 6.4 User Guide

117
Automate
 Activity Description
dataset, click Publishing Wizard and specify your publishing options.
See Dimensional Viewer for more information.
Notes:
l 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.
l 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.
l 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.
l Select the Log tab to review a script's execution log.
l 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.
l Automation does not support positional parameters denoted by a question mark.
l For each applicable activity, you can specify the database connection in the activity's properties. The
default connection is the currently-active open connection.
l The connection associated with an activity takes precedence over the connection information in a report
or SQL script, unless the report or SQL script information is a cross-connection query.

Use File Automation Activities

You can use the following activities in automation scripts.
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.

Activity Description
Copy File Copy or move one or more files to another location (for example, to a shared folder where
they can be accessed by your business community).
Review the following for additional information:
l Source file—Select the file to copy.
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 copy. To use this method:
1. Specify the file directory by browsing to and selecting one of the files.
2. Then modify the file name in the Source file field using wildcard characters
and/or variables.
l Copy to folder—Select the destination folder into which you want to copy or move
the file or files.
l Copy name—To rename the copied or moved file, enter a new name.
Note: This field is only applicable when copying a single source file.
l 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.

Toad Data Point 6.4 User Guide

118
Automate
Activity Description
l Delete source file—Select to move the file from the source folder to the destination
folder. Deselect to copy the file to the destination folder.
l Retry—Specify the number of times to retry the copy action if an error or timeout
occurs.
l Wait—Specify the number of seconds to wait between retries.
Delete File Delete one or more files from one or more specified locations. You can also use this activity
to delete files that are created previously in the script.
Files to delete—Click Add Files to select one or more files to delete. In the File Collection
Editor, do one or both of the following:
l To delete files created previously in the script, select a file in the left pane and click
Add.
Note: When a file name is displayed for selection in the left pane, any variable
names that are used in the file name are visible (#MyVariable#), but suffixes are not
visible.
l To select other existing files, click to browse to and select the files.
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 delete. To use this method:
1. Specify the file directory by browsing to and selecting one of the files.
2. Then modify the file name in the Files to Delete pane using wildcard
characters and/or variables.
Note: As an example of using wildcards and variables, using the file name
MonthlyReport_#var#*.xls, where #var# is Feb, would delete all reports whose file
name begins with "MonthlyReport_Feb" in the same directory.
Log Comment Writes a comment to the scripts's log.
Log message—Enter the text of the comment you want to insert into the log when this
activity executes.
Zip/Unzip Files Create a zip file (compressed folder) and add files to it. You can add files created previously
in the script to the zip file. Use this activity to zip multiple files and then attach the zip file to
an email using the Email activity.
You can also use this activity to unzip a zip file.
Review the following for additional information:
Zip Tab
l Archive name—Select a location and name for the zip file. You can use a variable in
this field by entering the hash symbol before and after the variable name
(#VariableName#).
l 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.
l Password—Enter a password if you want to password protect the zip file.
l Hide password—Select this option to mask password during input.
l Encryption—Select whether to create a zip file without encryption (Standard) or to
encrypt the zip file.
Note: If you encrypt the zip file, you must have a zip utility that can support 128-bit or
256-bit decryption.

Toad Data Point 6.4 User Guide

119
Automate
Activity Description
l Zip all files in this directory—Select a directory. All files in the directory will be added
to the zip file. You can use both this field and the Source files field to select files to
add to the zip file.
l Source files—Click Add Files to select one or more files to add to the zip file. In the
File Collection Editor, do one or both of the following:
l To add files created previously in the script, select a file in the left pane and
click Add.
Note: When a file name is displayed for selection in the left pane, any
variable names that are used in the file name are visible (#MyVariable#), but
suffixes are not visible.
l To select other existing files, click to browse to and select the files.
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 modify the file name in the Files to Archive pane using wildcard
characters and/or variables.
Unzip Tab
l Archive name—Select a zip file.
l Password—If password-protected, enter the password.
l Extract to folder—Select a location to extract the files to.
To learn how to use the Zip/Unzip Files activity, see the following:
l Automation and the Zip Activity (video)
Find and Replace This activity opens one or more files and performs find and replace actions inside the files.
You can write the results to a different location and file name.
Review the following for additional information:
l Find/Replace—You can have multiple Find/Replace tasks within one activity. You
can specify a different source file, save file, and action for each Find/Replace task.
l Source file—Select the file in which to perform the find and replace action for the
selected Find/Replace task.
In this field, you can also use the asterisk (*) and question mark (?) wildcard
characters in the file name to help you select one or more files (in the same
directory). To use this method:
1. Specify the file directory by browsing to and selecting one of the files.
2. Then modify the file name in the Source file field using the wildcard
characters.
l Find—Enter the content to find.
l Replace with—Select the type of replacement content: a value, contents from a file,
or a variable value.
l Set value—Enter the replacement value, select the replacement variable, or select a
file containing replacement content.
l Save file—Select a location and file name.
The source file is selected by default.

Toad Data Point 6.4 User Guide

120
Automate
Activity Description
l 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.
Note: Use drag and drop or up/down arrows to change the order of multiple Find/Replace
tasks in one activity.
To learn how to use the Find and Replace activity, see the following:
l Automate Find and Replace (video)
FTP File Upload or download one or more files using an FTP or SFTP connection. Review the
following for additional information:
l Select an FTP connection—Select an FTP connection or define a new connection.
Note: You can define a new connection prior to using the FTP activity by selecting
View | FTP Connections.
l Transfer ONLY if—Select this option to set conditions for FTP transfer, and then
select a condition from the list. If you select this option, the FTP activity only runs if
the condition you specify is met.
l Local directory exists—Select this option to transfer only if a local directory
exists, then browse to and select the directory.
l Local file exists—Select this option to transfer only if a local file exists, then
enter the name of the local file.
l Remote directory exists—Select this option to transfer only if a remote
directory exists, then browse to and select the directory.
l Remote file exists—Select this option to transfer only if a remote file exists,
then enter the name of the remote file.
l Define Operations—Click to specify the files and other options for the FTP upload or
download task. Review the following for additional information:
o Operation—Select Upload or Download.
o Use file mask—Select this option if you want to select files by using a file
mask. Then specify file mask filtering options in the File Mask fields at the
bottom of the page.
o Move files—Select this option to move the files from the source location to
the target location instead of copying them.
l Retry—Specify the number of times to retry the FTP connection if it fails.
l Wait—Specify the number of seconds to wait between retries.
To learn how to use the FTP activity, see the following:
l Automate FTP Upload and Download (video)
Publish Files Publish one or more files to Intelligence Central. You can select files created by previous
activities in this script or other existing files.
Review the following for additional information:
Files to Publish—Click Manage Files to select files created previously in this script or to
browse for other files to publish. In the File Collection Editor do one or both of the
following:
l Select one or more previously-created files in the left pane and click Add.
l Click and then browse to select an existing file.

Toad Data Point 6.4 User Guide

121
Automate
 Activity Description
Publishing Options—Click Publishing Wizard to select an Intelligence Central connection
and specify publishing options.
See Publish to Toad Intelligence Central for more information.
Notes:
l 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.
l 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.
l 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.
l Select the Log tab to review a script's execution log.
l 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.
l Automation does not support positional parameters denoted by a question mark.

Tips:

l
For activities in which you can add a suffix to the output file, click to build a suffix using the
expression editor.
l You can use the expression editor to select a variable created in a previous activity or select one of your
stored bind variables.

Use System Automation Activities

You can use the following activities in automation scripts.
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.

Activity Description
Set Variable 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:
l 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#).
l Variable type—Specify a variable type.
l SQL—Select SQL if you plan to use the result set of a query as the variable
value.
l DateTime—See Using Variables in Automation for more information about
date variables.

l
Variable value—Enter a value or click to build an expression.

Toad Data Point 6.4 User Guide

122
Automate
Activity Description

l
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.
l 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" on page 127 for more information
about SQL type variables.
l 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.

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

to reorder list.
l Define variable prompt values – This option is used for creating a custom list of
variable values. It is enabled by selecting the Prompt during run checkbox. After
the checkbox is ticked, the Define variable prompt values link appears along with a
field for selecting the connection that will be used to run the query that populates
the list of predefined values for the specific variable.
To learn more about variables, see the following:
l Using Variables in Automation
l Automation and Variables (video)
Set Variable Value 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:
l Variable name—Select a previously-defined variable.
l Variable type—Displays the variable type of the selected variable.

l
Variable value—Enter a new value or click to build an expression.
l 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.
If..Condition.. 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:
l Using the If Condition in Automation (video)
Use the expression editor to build an expression that evaluates a condition. Typically this
includes a variable (user-defined or built-in).
l 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.

Toad Data Point 6.4 User Guide

123
Automate
Activity Description
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:
l Using Variables in Automation
l Automation and Variables (video)
Loop Dataset 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:
l Automation and the Loop Dataset Activity (video)
Review the following for additional information:
l Dataset variable—Enter a new name for the dataset variable or use the default
name.
l Select dataset variable—(Optional) You can select one of the SQL-type variables
created in a previous activity to use as the dataset variable.
l Connection—Select a connection.
l 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.
l Click the drop-down list to select a file from the currently open project in the
Project Manager.
l Click to browse to and select a SQL file.

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

l Link SQL file—Select to link the SQL script file you selected in the previous step.
l SQL script—Enter a SQL statement to use as the driving query (if you did not
select a SQL file).
l 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" on page 127 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
While.. 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:

Toad Data Point 6.4 User Guide

124
Automate
Activity Description
l Using the While Loop in Automation (video)
Use the expression editor to build an expression that evaluates a condition. Typically this
includes a variable (user-defined or built-in).
l 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.
l 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:
l Using Variables in Automation
l Automation and Variables (video)
Send Email 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:
l 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).
l To—Enter an email address or click to auto-populate this field with your email
address.
l 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.
l Email Server Settings—Click this button to specify your SMTP email server
settings.
l 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).
l 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.
l Subject, Body—Enter a subject line and enter text for the body of your email.
l 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:
l Select one or more previously created files in the left pane and click Add.

Toad Data Point 6.4 User Guide

125
Automate
Activity Description
l 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.
l 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.
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:
l Automation and Email (video)
Run Program Run a program with optional command-line arguments. Review the following for additional
information:
l Program—Select a batch file (.bat) or executable (.exe) file.
l 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.
l Run directory—Select the directory to change to, when running the program.
l Run style—Select the window state for the program when it runs.
l Verb—Enter verbs used when running the program, such as "Print".
l Wait for program to exit—Select this option to pause if there is an error. Select
duration of wait time in minutes.
l 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 Automation Run another automation script in your current automation script.
Script Review the following for additional information:
l Automation script—Select an automation script (.tas file).

l
After selecting a script, click to open the child script in a new window.
l 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

Toad Data Point 6.4 User Guide

126
Automate
 Activity Description
value is stored in this variable. Return code values are:
o 0 = Successful execution
o 999 = An error occurred during execution
o 111 = An error occurred, but you selected continue on error.
You can use this variable with the If Condition activity.
Pause 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:
l Using the While Loop in Automation (video)
Group Activities Use this activity to group and organize two or more activities together.
Throw Error 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.
Parallel 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:
l 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.
l 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.
l 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.
l Select the Log tab to review a script's execution log.
l 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.
l Automation does not support positional parameters denoted by a question mark.

Using Variables in Automation

Automation variables are a powerful tool that can greatly extend your scripts. Variables can add flexibility and
customization. Variables can be used in almost every Automation activity.

Watch Using Variables in Automation for a video demonstration of this feature.

Toad Data Point 6.4 User Guide

127
Automate
How to Create Variables
User-Defined Variables
You can use the Set Variable activity to create user-defined variables. See "Use System Automation Activities" on
page 122 for more information.
Built-In Variables
Several Automation activities create variables by default. The following built-in variables are created by Automation
activities.
l RowCount variable— This integer type variable is created by several activities. The value assigned to this
variable is the number of rows returned by the query in the activity.
l Return code variable— This integer type variable is created by the Run Automation Script and the Run
Program activities. The variable value is assigned based on the execution status of the script or program.
l Built-in SQL type variables—The Loop Dataset and Execute Script activities create a SQL type variable
which is used to store the result set of a query. The Loop Connections activity creates a SQL type variable
with only one row containing connection information.
l Activity Result variable—This variable captures the result of each activity's execution. If an error occurs, it
captures the error message. The name of this variable is _ACTIVITY_RESULT.
The Automation script creates the following built-in variables.
l Root path variable—This string type variable is created by the script. The value assigned to this variable is
the path you specified in the Root path field in either the Test environment or the Production environment
area in script Settings. The path that is applied depends on which environment (Test or Production) is
selected at the time the script is executed.
l Run mode variable—This string type variable is created by the script. The value assigned to this variable is
either Test or Production, depending on the environment selected in the script Settings at the time the script
is executed.

How to Use Variables

Where to Use Variables

The following are some examples of where you can use a variable (built-in or user-defined) in an Automation script.
l In the file name of a file exported by an activity.
l In the suffix appended to a file name.
l In an expression used in the While or If Condition activities.
l In a SQL statement as a bind variable for filtering data.
l In a SQL statement as a bind variable in an Import or Export template.
How to Use a Variable
To express the value of a variable, surround the variable name with hash symbols (#), for example #myvar#. The
following are some examples of how variables might be used.
#myvar#_report.xlsx
Report_#Dept_Name#.xlsx
#File_1_RCOUNT# > 0
It is important to remember that when a variable is expressed, the variable value is substituted as text in place of the
variable name (#myvar#). The only exception is when you use a variable as a bind variable in a SQL statement.

Toad Data Point 6.4 User Guide

128
Automate
To use a variable as a bind variable in a SQL statement, use the following syntax format (regardless of what the
address_id column type is):
SELECT * FROM contact WHERE address_id = :myvar
You can also use the Automation variable here as text substitution, but in this case single quotes might be required
(because it is a text substitution) depending on the column type in the WHERE clause.
For example, if last_name is a character column, use the following syntax (single quotes are required):
SELECT * FROM contact WHERE last_name = ‘#myvar#’
If address_id is a non-character column, use the following syntax:
SELECT * FROM contact WHERE address_id = #myvar#
How to Use a String Variable in a Comparison Expression
When comparing a string variable to a string value in an expression, always surround the variable name and the
string value with single quotes, for example:

'#myvar#' = 'Dog'
About SQL Type Variables
SQL type variables are created in two ways:
l You can create user-defined SQL type variables using the Set Variable activity.
l A built-in SQL variable is created by the Loop Dataset activity, the Execute Script activity, and the Loop
Connection activity.
If you ask just for the variable value of a SQL type variable, Toad returns the value from the first row and column of
the result set. However, you can filter by column using this syntax:
<variable name>.<column name>.
For example, Region.Region_Name returns the first value (first row) in the column Region_Name.
Dataset/SQL Variable - Special Format for Filtering
You can use the following syntax format to filter data from the dataset variable (or any SQL type variable):
<dataset variable name>.<filter parameter>
Example: Loop_data_1_SQL.region_id, where region_id is one column in the dataset variable

For example, use this format in a SQL statement (in a Select to File activity) to select and export data based on each
region_id value in the dataset variable. The statement is executed with each loop of the Dataset activity.
SELECT * FROM address WHERE address.region_id = :Loop_data_1_SQL.region_id
SELECT * FROM address WHERE address.region_id = #Loop_data_1_SQL.region_id#
Bind Variables
You can store global bind variable/value combinations in Toad . After storing a global bind variable, that variable is
available to use in Automation. Your stored global bind variables appear in the list of variables in the expression
editor of applicable activities.

How to Create and Use Date Variables

In Automation scripts, a common use case is to create a variable to be used later in the script in a SQL statement
(such as in the Select to File activity). When creating a variable for a date value, there are two variable types to
choose from in the Set Variable activity: String or DateTime. The type you choose depends on how the value is to be
used in the SQL.
l If the SQL expects a string value, use the String variable type.
l If the SQL expects a date/time value, use the DateTime variable type.

Toad Data Point 6.4 User Guide

129
Automate
How to Define a Date Variable

In the Set Variable Activity

Valid
Example SQL Variable Type Values
SELECT * FROM contact String '2017-7-7'
WHERE birth_date < to_date(:var_date, 'YYYY-MM-DD')
and CONTACT_ID < 20
SELECT * FROM contact DateTime To_date
WHERE birth_date < :var_date and CONTACT_ID < 20 ('2017-07-
07', 'YYYY-
MM-DD')
'2017-7-7'

Using a String. For a String variable type, surround the date value in single quotes. This ensures that date
delimiters, such as the dash (-) or forward slash (/), do not cause Toad to interpret the value as an expression.
‘2017-7-7’ is treated as a string
2017-7-7 is evaluated to 2003

Variable Scope
When you are building an Automation script that uses a variable, it is important to understand the variable's scope of
visibility. This section describes the concept of variable scope in Automation scripts.

Terms and Definitions

Term Definition
Container Let's define a container as any one of the following:
l The script itself
l An activity that contains other activities (like the Group, While, or Loop Dataset
activities)
l A branch of an activity (for example, a branch in the If Condition activity)
Levels Suppose that a script is composed of nested containers. Each container is a different
level with respect to variable scope.
Level 1—The outermost container is the script itself. The script is comprised of
activities. The script and its activities in the main workflow comprise the first level.
Level 2—Some activities can contain other activities. "Container activities" (such as
the Loop Dataset) are at the second level. Level 2 is nested below level 1.
Level 3—Container activities can contain branches. A branch within a container
activity is the third level. Level 3 is nested below level 2.
And so on, until we get to the innermost level.
Container Activity A container activity is an activity that contains other activities. The Loop Dataset,
Group, If Condition, While, and Parallel activities are container activities.
Set Variable Activity Use this activity to define a variable.
Set Variable Value Use this activity to assign a new value to a previously-defined variable.
Activity

Toad Data Point 6.4 User Guide

130
Automate
Scope Rules
Basically, the scope of a variable includes all activities subsequent to the variable-defining activity AND either at or
nested below the level at which the variable is defined.
The following are the rules for variable scope.
l Use a variable in an expression—You can use a previously-defined variable in an expression in an
activity if the following are true:
l The expression activity is subsequent to the variable-defining activity
l AND the expression activity is at the same level or at a level nested below the variable-
defining activity
l Reassigning a variable value—You can reassign the value of a variable (except a SQL type variable) at
any point within the variable's scope. This task is accomplished using the Set Variable Value activity. See
"Use System Automation Activities" on page 122 for more information.
Once a variable value is reassigned, the new value applies to activities subsequent to the original Set
Variable activity AND either at the same level or nested below the level of the original Set Variable activity.
Example: You can use the Set Variable Value to turn a previously-defined variable into a counter
(var=var+1) in the Loop Dataset activity.
l Define a new variable with the same name—You can redefine a new variable with the same name, but
only within an activity that is nested one or more levels below the variable-defining activity. For example, if
you defined a variable in the main workflow of your script (first level), you can define a new variable with the
same name within a container activity (second level). The scope of this variable value is restricted to that
container activity.
You cannot define a new variable with the same name at the same level as the original.
Additional Examples to Illustrate Scope
l Run Automation Script activity—This activity is used in an Automation script (the parent script) in order to
execute another Automation script (the child script). However, a variable defined in the parent script cannot
be used in the child script.
l Loop Dataset activity—This activity is a container activity and can contain other activities, including a Set
Variable activity. However, a variable defined in a Set Variable activity within the Loop Dataset cannot be
used outside the Loop Dataset.

Execute and Schedule Automation Scripts

You can execute automation scripts on demand from the Automation window or the Project Manager. You can also
schedule scripts to run on a recurring basis at a specific time. For example, schedule a script that performs long-
running tasks to run when database activity is light.
When you schedule a script in Toad, the Job Manager creates a Windows scheduling task. Scheduled scripts run in
batch mode. If using another scheduling application, specify "-batch=true" in the command. This executes the
script in non-visual (batch) mode. See the procedure below.

To execute a script from the Automation window

1. Click Run in the Wizard bar. The script is compiled and executes immediately.
2. To view the script's execution log, select the Log tab.

Toad Data Point 6.4 User Guide

131
Automate
 3. If you want to schedule the script, first resolve any problems that caused activities to fail or disable failed
activities. An activity can be disabled from its Activity Info window.
Tip: To execute a script from the Project Manager, right-click the script and select Execute.

To schedule an automation script

1. With a script open in Automation, click Schedule in the Wizard bar. A Windows Scheduler task is created for
the script and a task properties dialog opens.
2. To schedule the script, select the Triggers tab. Select the scheduling trigger for this task and click Edit.
3. In the Edit Trigger dialog, specify scheduling details. Click OK to save your changes and close the dialog.
4. To run the script when you are not logged on, in the General tab select Run whether user is logged on or
not. Then enter your Windows credentials when prompted.
5. When finished, click OK to save your changes and close the task properties dialog. For detailed information
about the task properties dialog, see "Schedule Windows Tasks" in the online Help.
Note: To run a scheduled script against a password-protected database, the password must be saved in the
Connection Properties dialog.

To execute a script in batch mode

l To start Toad and execute a script in batch mode (non-visual mode), enter the following command
and arguments:
<path to the toad.exe application> -batch=true “<path to Automation script>”
For example:
C:\Program Files\Quest Software\Toad Data Point 6.4\toad.exe -batch=true "<path\scriptfilename.tas>"
Notes:
l Automation scripts are compatible with the version of Toad in which they are created. To learn how to
upgrade your scripts and migrate scheduled tasks that were created in a previous version of Toad, see
"Upgrade Scripts and Scheduled Tasks" in the online Help.
l The Automation module is disabled if Toad was installed with the Prohibit saving passwords
option selected.

Toad Data Point 6.4 User Guide

132
Automate
 How to configure Toad to use Auth for
sending SMTP Emails

To configure Toad to use SMTP Emails, we need to get the main data from the following third parties:
l Azure Portal where we register our application
l O365 Admin Center with at least one O365 License E5 where we register the O365 user
l Microsoft Exchange to configure our settings at a higher level

Configuration Steps:
1. First, we register our application in Azure Portal | App Registration.

Toad Data Point 6.4 User Guide

133
How to configure Toad to use Auth for sending SMTP Emails
2. In the O365 Admin Center, let’s go to create the user (toad in this case) that will use the application.

Toad Data Point 6.4 User Guide

134
How to configure Toad to use Auth for sending SMTP Emails
3. In Exchange Admin Center | Settings, make sure the Mail Flow has the settings below at the organization
level.

Toad Data Point 6.4 User Guide

135
How to configure Toad to use Auth for sending SMTP Emails
4. At this point, Microsoft Documentation states that the application registered has to interface
and use the SMTP server.

This refers to the setting from the MS Entra ID | Enterprise Application registration, NOT from App
Registration. To do this it’s necessary to run these PowerShell commands in the order below:

NOTE: The OBJECT_ID is the Object ID from the Overview page of the Enterprise Application node (Azure
Portal) for the application registration. It is not the Object ID from the Overview page of the App Registrations
node. Using the incorrect Objec ID will cause an authentication failure.

New-ServicePrincipal -AppId d41b4835-027d-4efd-99be-82607a675c5c -ObjectId

779826e8-397c-4c74-b3a5-c874c82ed4af <-- This is the Object_ID from
Enterprise Application
Add-MailboxPermission -Identity "toad@questmail.onmicrosoft.com" -User
d41b4835-027d-4efd-99be-82607a675c5c -AccessRights FullAccess
Get-ServicePrincipal | fl
Set-CASMailbox -Identity toad@questmail.onmicrosoft.com -
SmtpClientAuthenticationDisabled $false
Get-CASMailbox -Identity toad@questmail.onmicrosoft.com | Format-List
SmtpClientAuthenticationDisabled

5. In Toad Data Point (this example), let’s set the email server specification (TenantID, ObjectID, and
Secret_key).

Toad Data Point 6.4 User Guide

136
How to configure Toad to use Auth for sending SMTP Emails
Even if there is something small issue to define, the email is sent and received in my inbox.

Toad Data Point 6.4 User Guide

137
How to configure Toad to use Auth for sending SMTP Emails
 About Us

Quest creates software solutions that make the benefits of new technology real in an increasingly complex IT
landscape. From database and systems management, to Active Directory and Office 365 management, and cyber
security resilience, Quest helps customers solve their next IT challenge now. Around the globe, more than 130,000
companies and 95% of the Fortune 500 count on Quest to deliver proactive management and monitoring for the
next enterprise initiative, find the next solution for complex Microsoft challenges and stay ahead of the next threat.
Quest Software. Where next meets now. For more information, visit www.quest.com.

Contact Quest
For sales or other inquiries, visit www.quest.com/contact.

Technical Support Resources

Technical support is available to Quest customers with a valid maintenance contract and customers who have trial
versions. You can access the Quest Support Portal at https://support.quest.com.
The Support Portal provides self-help tools you can use to solve problems quickly and independently, 24 hours a
day, 365 days a year. The Support Portal enables you to:
l Submit and manage a Service Request
l View Knowledge Base articles
l Sign up for product notifications
l Download software and technical documentation
l View how-to-videos
l Engage in community discussions
l Chat with support engineers online
l View services to assist you with your product

Toad Data Point 6.4 User Guide

138
About Us
 Index

cross-connection queries 40
A
activities, automation 107 D
automation 107 data
database activities 110 browse parent tables 36

execute automation scripts 131 compare differences 66

file activities 118 export in one click 66
system activities 122 in result sets 64
variables 127 reports tutorial 86
Database Diagram
B
tutorial 34
bind parameters 47
Database Explorer
bind variables 47
best method for locating objects 27
build queries
filter databases and objects 28
create cross-connection queries 40
search for objects 33
reverse engineer queries 42
database objects 27
C filter schemas 28
code object explorer 27
automating 107 search for 33
set bind variables 47 databases
shortcut keys 61 automation activities 110
view result sets 64 Diff Viewer, data 66
connections
E
create ODBC 10
Editor 59
create Oracle 12
reverse engineer queries 42
create SharePoint 20
set bind variables 47
create Snowflake 15, 25
shortcut keys 61
create SQL Azure 17
Excel
create SQL Server 17
add to Report Manager 90
share for all session windows 23
share linked queries 68
troubleshoot 24
export
troubleshoot SQL Server 24
data with one click 66
understand 23

Toad Data Point 6.4 User Guide

139
Index
F Q
files Query Builder
automation activities 118 create cross-connection queries 40
filter reverse engineer queries 42
databases and objects 28 tutorial 38

H R
heterogeneous queries 40 reports 90
data tutorial 86
I
DB2 91
Intelligence Central
manage reports 90
publish objects to 72
MySQL 93
intellisense
ODBC 93
shortcut keys 61
Oracle 93
K publish and share 97
keyboard SQLServer 95
editor shortcuts 61 result sets 64

L reverse engineer queries 42

linked queries 68 S

M script results 64
scripts
Master Detail Browser 36
automating 107
O
search 33
Object Explorer 27 database objects 33
filter databases and objects 28 SharePoint
search for objects 33 create connections 20
object search 33 shortcut keys 61
ODBC Snowflake
create connections 10 create connections 15, 25
One Click Export 66 SQL
Oracle automating scripts 107
Oracle, create connections 12 shortcut keys 61

P SQL Azure
create connections 17
parameters 47
SQL Editor 59
bind 47
SQL Server
create connections 17
troubleshoot 24

Toad Data Point 6.4 User Guide

140
Index
subqueries
tutorial 44
system automation activities 122

T
tables
browse data in related 36
troubleshoot
publishing to TIC 83
SQL Server connections 24
tutorials 86

create database diagrams 34

create ODBC connections 10
create Toad data reports 86
subquery 44
visually build queries 38

V
variables
Automation 127
bind 47

Toad Data Point 6.4 User Guide

141
Index