› Workflow › Business processes › Design a basic process › Add an activity › Activity types › Common activities › Data access activity › Get data from stored procedures of a database

Get data from stored procedures of a database

Create a database query to get data from the stored procedures of a database.

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
  1. On the properties panel of the Data access activity, select Configure.
    The Data access configuration - <Activity name> dialog box is displayed.
  2. 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.

  3. 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).

  4. On the Type list, select Stored procedures.
  5. On the Stored procedures list, select the database procedure to be used in the query. For example, to get the stored procedure for the "jobs duration overrun" query, select awsp_GetJobsDurationOverrun.
  6. Map the parameters (members/properties) using dynamic or static values.
    It is mandatory to specify values for all the columns/parameters.
  7. On Return result set to, select a variable.
    At runtime, the data in the stored procedure is retrieved and appears in the respective columns of the table.
  8. Select OK.