Get data from a database view

Create a database query to get data from a database view.

Before you begin

To use the Data access activity, you must create a process variable to hold the database connection string. For example, create a process variable, with the following settings:

ID = DBConnectionString
Type = String
Value = DSN=TotalAgility

Procedure

On the properties panel of the Data access activity, select Configure.
The Data access configuration - <Activity name> dialog box is displayed.
You can select one of the following for Database connection.
- Local (Default): Select the process or server variable to specify the database connection string that holds the details of the relational database.
- External: To use the database connection from external storage, enter the secret name for the connection secret stored in the configured external storage.
A list of schema that the database user has permission to access is displayed.
On the Schema list, select a database schema to use for retrieving database objects.

If no schema is selected, the system uses the default schema for the database user (typically dbo for SQL Server).
On the Type list, select Views.
On the Views list, select the view to retrieve records for. All existing views are listed and all the columns for the views appear under Available columns.
On the Available columns list, select the columns to add records to and select .

To get the records for the selected view, select one of the following options on the Return list.

Option	Description
Multiple rows	Retrieves multiple rows. Map the Return result set to output parameter to the dynamic complex variable. Map the Return row count to output parameter to the long variable. See Create process variables.
Single row	Returns a single row. (Default: Single row)
Use aggregate	Retrieves the minimum and maximum values and the row count. To retrieve the minimum value of a column, select Min value for the column, and on the Output min value to list, select the variable that holds the minimum value. To retrieve the maximum value of a column, select Max value for that column, and on the Output max value to list, select the variable that holds the maximum value. To get the count of rows within a table, select Count rows and on the Return row count to list select the variable that holds the count of rows.

Option

Description

Multiple rows

Retrieves multiple rows.

Map the Return result set to output parameter to the dynamic complex variable.
Map the Return row count to output parameter to the long variable.

See Create process variables.

Single row

Returns a single row. (Default: Single row)

Use aggregate

Retrieves the minimum and maximum values and the row count.

To retrieve the minimum value of a column, select Min value for the column, and on the Output min value to list, select the variable that holds the minimum value.
To retrieve the maximum value of a column, select Max value for that column, and on the Output max value to list, select the variable that holds the maximum value.
To get the count of rows within a table, select Count rows and on the Return row count to list select the variable that holds the count of rows.

You must map all the output parameters with the relevant variables.
ODBC and OLEDB do not support xml data type.

Configure the query in the same way as for retrieving a table. See Get records from a database table.
Select OK.