Skip to content

Knowledge Base

Business Process Automation Platform

Call Stored Procedure (OLEDB) Tool

Introduction to the Call Stored Procedure (OLEDB) Tool

What is the Call Stored Procedure (OLEDB) Tool?

The Call Stored Procedure (OLEDB) tool executes database stored procedures and functions to provide read, write, and automated data processing.

If a procedure generates a recordset, the task step can return this to other steps for further processing. Conversely, the task step can consume a recordset to enable multiple procedure calls or execute a single procedure. Procedure calls can be passed static or dynamic parameters to control the precise nature of the call being made.

The features provided by the Call Stored Procedure (OLEDB) tool mean that it may be used as a Data Connector, Input, Output, or Execute type step within a task.

The tool supports any RDBMS with a functional OLEDB provider, enabling native support for a multitude of data sources.

Call Stored Procedure (OLEDB) Tool Features

  • Calls stored procedures from a multitude of data sources
  • Allows manual or dynamic inputting of procedure parameters
  • Contains memory features to memorise task data created at run-time
White Paper - Call Stored Procedure (OLEDB)

White Paper - Call Stored Procedure (OLEDB)

The Call Stored Procedure (OLEDB) white paper introduces you to the tool.

Download White Paper

Technical Summary

Working with Other Tools

The Call Stored Procedure (OLEDB) tool can interact directly with the following tools, whether by exposing information to it or consuming information from it:

Consuming from Other Tools

The Call Stored Procedure (OLEDB) tool can consume output from the following tools:

IconTool NameTool Category
Import Flat File ToolImport Flat FileInput
Convert XML to Recordset ToolConvert XML to RecordsetFormat
Call Stored Procedure (OLEDB) ToolCreate Workflow JobFormat
Call Stored Procedure (OLEDB) ToolFormat as Flat FileFormat
Call Stored Procedure (OLEDB) ToolFormat as HTMLFormat
Call Stored Procedure (OLEDB) ToolFormat as HTML ProData Connectors
Call Stored Procedure (OLEDB) ToolFormat as TextFormat
Run Crystal Report Tool - Crystal Reports AutomationRun Crystal ReportFormat
Run Microsoft Reporting Services ToolRun Microsoft Reporting ServicesFormat
Run Microsoft Word (Merge) ToolRun Microsoft Word (Merge)Format
Call Stored Procedure (OLEDB) ToolFilter DataGeneral

Objects Consumed

The following objects can be consumed by a Call Stored Procedure (OLEDB) step:

  • Recordset — Information structured into columns and rows
  • Variables (Text) — Textual data is extracted from selected Variables
  • Documents (Text) — One or multiple plain text documents
  • Documents (HTML) — One or multiple HTML documents
  • Documents (Paged HTML) — One or more multiple paginated HTML documents
  • Documents (PDF) — One or multiple PDF documents
  • Documents (RTF) — One or multiple RTF documents
  • Documents (XML) — One or multiple XML documents
  • Documents (CSV) — Output from a Run Crystal Report Step

Exposing to Other Tools

The following tools can consume output from a Call Stored Procedure (OLEDB) task step. Note that the below depends whether recordsets, variables, or XML is outputted.

IconTool NameTool Category
Call Stored Procedure (OLEDB) ToolConvert Recordset to XMLFormat
Convert XML to Recordset ToolConvert XML to RecordsetFormat
Call Stored Procedure (OLEDB) ToolCreate Workflow JobFormat
Call Stored Procedure (OLEDB) ToolTransfer File (FTP)Output
Run External Program ToolRun External ProgramExecute
Call Stored Procedure (OLEDB) ToolFilter DataGeneral

Objects Exposed

The following objects, exposed by the Call Stored Procedure (OLEDB) step, can be consumed by the above tools:

  • Recordset — If an Input Recordset has been selected from a previous task step, this is supplied as the RecordSource to the consuming task.
  • Document Source — If a Document Source has been selected from a previous task step, this is supplied as a DocumentSource to the consuming task.
  • Memory Definitions — Displays a list of Memory Definitions created for the step and the columns to be memorised for each one. Double-click on Memory Definition to edit the details.
  • Step Properties — A number of properties for the step are exposed in the Environment tab of the Task Browser dialog box.

Prerequisites

The Call Stored Procedure (OLEDB) tool requires the following:

  • Functional OLEDB provider which can expose procedures and or functions

Adding OLEDB Connections

Before adding the Call Stored Procedure (OLEDB) tool to a task, connections to OLEDB data sources must be configured. Once configured, all connections can be used by any Call Stored Procedure (OLEDB) step.

Call Stored Procedure (OLEDB) Tool

You open the OLEDB Connections dialog box by either:

  • Going to Manage > Tools >, then either Input > Call Stored Procedure (OLEDB), Data Connectors > Call Stored Procedure (OLEDB), Output > Call Stored Procedure (OLEDB), or Execute > Call Stored Procedure (OLEDB)
  • From the Task Manager, expanding BPA Platform > System > Tools > Input, Data Connectors, Output, or Execute and double-clicking Call Stored Procedure (OLEDB) in the Items List

Use Properties to edit an existing connection, Remove to delete an existing connection, and Test to ensure the highlighted connection can be successfully made.

Click Add to create a new connection; select the required ODBC driver or data source.

Selecting an OLEDB Driver

Once a driver has been selected, add the connection details to the database. The configuration window is different for every driver available; the following example is shown when an SQL Server Native Client driver has been selected:

Call Stored Procedure (OLEDB) Tool

Editing OLEDB Connection Properties

After establishing the basic connection to the relevant OLEDB driver or data source, you can add more advanced configuration, if required. Highlight the relevant connection and click Properties.

General Tab Properties

Call Stored Procedure (OLEDB) Tool

  • Name — The connection name
  • Additional Providers Information — Where available, additional information for the connection is displayed

Connection Tab Properties

Call Stored Procedure (OLEDB) Tool

  • Connection String — Contains the full connection string.
  • Use Pass-Through — This property is only available with some drivers. You can choose to make the connection directly to the driver (enabled), or use the Microsoft Data Access Objects (DAO) API to make the connection to the driver.
  • Reconfigure — If any changes are required, use Reconfigure to open the configuration window.
  • Test — Tests the connection to the database.
  • Connection Settings — If required, you can set a time-out period for connecting to the database in Seconds.

Cursors Tab Properties

Use the properties in the Cursors tab to change the type of database cursor used when executing queries.

Call Stored Procedure (OLEDB) Tool Connection SQL Server

  • Basic forward only — Use this option where you expect the task to only use the results from the Call Stored Procedure (OLEDB) step once. The result set from the queried rows can only be read top-to-bottom. The cursor cannot be reset so the query cannot be re-used. All drivers support this mode.
  • Advanced cursor attributes
    • Re-usable (scrollable) — This property allows the cursor to move backwards and forwards through the result set, thus the Call Stored Procedure (OLEDB) output can be consumed by multiple steps in the same task.
    • Do not reflect changes in underlying data source — The rows returned by a query are cached independently at the time the query is executed. When the result set is actually read, any changes made to the source data since the query was executed are not reflected in the result set.
    • Reflect row deletions and changes only — Any changes made to the source data since the query was first executed (updates and deletes to rows in the result set only) are reflected.
    • Reflect row deletion, changes and additions — Any changes made to the source data since the query was first executed (updates, inserts, and deletes) are reflected in the result set.

SQL Tab Properties

The properties available in the SQL tab set the supported rules for the selected OLEDB driver for the SQL statements.

Call Stored Procedure (OLEDB) Tool

For more information, refer to your OLEDB driver documentation.

Step Configuration

Even though the Call Stored Procedure (OLEDB) tool is found under three different tool categories, they do not affect the functionality of the tool.

To add a new Call Stored Procedure (OLEDB) step to an existing task, you either:

  • Click and drag the Call Stored Procedure (OLEDB) icon from the Task Browser to the task Design area.
  • From the task’s Design tab, right-click on empty space and select Add > then either Data Connectors > Call
    Stored Procedure (OLEDB), Execute > Call Stored Procedure (OLEDB), Input > Call Stored Procedure (OLEDB)
    , or Output > Call Stored Procedure (OLEDB).

For a detailed description of how to create new tasks, refer to the product help.

About the General Tab

Call Stored Procedure (OLEDB) Tool

The General tab is used to enter the following details for the step:

  • Name — Enter a meaningful name for the step
  • Description — If required, enter a description of this step
  • Use a Recordset — Enable this parameter if recordset data from a previous task step is required when executing the stored procedure
    • Input Recordset — Contains all available recordsets from steps previously created in the task
  • Read documents from a source — Enable this parameter if document data from a Format task step is required when executing the stored procedure
    • Document Source — Contains all available documents from Format steps previously created in the task; note that the document format is displayed after the step name

About the Main Tab

Use the Main tab to choose the relevant stored procedure for this task step.

Call Stored Procedure (OLEDB) Tool

Choose the OLEDB Connection used for this task step— these are the connections created in the global configuration.

The Call Stored Procedure (OLEDB) tool queries the database and retrieves all available stored procedures— click Browse to choose the required procedure.

Parameters associated with the procedure are displayed in the table below. Drag-and-drop variables, recordset columns, or step properties to the Value column to map to the procedure parameters at task run-time. If any new parameters were added since the Call Stored Procedure (OLEDB) step was first added to the task, click Refresh to pull those into this task step.

Use Memory to memorise some or all of the step output for use in other task steps. This is particularly useful when the Call Stored Procedure (OLEDB) step is called multiple times — use Memory to compare the record or document previously processed to the new record or document currently being processed to avoid duplicates.

About the Recordset Tab

If the stored procedure selected in the Main tab only writes data to the database, the parameters in this tab can be ignored. If the stored procedure reads data, use this tab to check the recordset structure created by the Call Stored Procedure (OLEDB) tool:

Call Stored Procedure (OLEDB) Tool

Enable This procedure returns a recordset and click Execute. You are prompted for confirmation to execute the procedure; click Yes.

If the stored procedure requires parameter values to execute the procedure, you are prompted to enter them:

Call Stored Procedure (OLEDB) Tool

At this point, you cannot use variables, recordset columns, or step properties. In Value, type in the test parameter values where required. The procedure is executed and the results displayed in the Results tab:

Call Stored Procedure (OLEDB) Tool

with the Columns tab showing the returned recordset columns:

Call Stored Procedure (OLEDB) Tool

About the Options Tab

The Options tab allows you to define how errors in this step are handled at task runtime.

Call Stored Procedure (OLEDB) Tool

If an error occurs, you can decide whether the step should Continue processing, or terminate the step immediately (Abort Step).

If the step is aborted, you can choose to Continue processing onto the next step in the task, or terminate the whole task immediately (Abort Task).

White Paper - Call Stored Procedure (OLEDB)

White Paper - Call Stored Procedure (OLEDB)

The Call Stored Procedure (OLEDB) white paper introduces you to the tool.