Stored Procedure Action

Declaration

<AMSQLQUERY CONNECTIONSTRING="text" RESULTDATASET="text" SQLSTATEMENT="text" MAXROWS="number" LOGIN="yes/time" TIMEOUT="number" CACHEDAFTER="datetime">

See Also

Stored Procedure Action-Setting Properties, Loop Dataset, Open SQL Connection Action, Close SQL Connection Action, Execute SQL Query Action

Description

Executes the selected stored procedure via OLEDB on the datasource specified. Stored procedures are configured on the database server and are often faster than repeated SQL calls.

Practical Usage

Useful for automated retrieval, updating or transfer of data. Could also be used to test database response times and size quotas.

IMPORTANT: Because the results retrieved may contain multiple records (think of records as rows) and multiple fields (think columns) - to access data retrieved from SQL Query you must use the Loop Dataset Action to iterate the records and embedded expressions to extract the data from the individual fields (e.g. %datasetname.fieldname% ). It sounds more difficult than it actually is - see notes below for more information.

Parameters

General Tab

Connection String

Text, Required
MARKUP:
a) CONNECTIONSTRING="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\Common Files\Borland Shared\Data\BCDEMOS.mdb"

Specifies an OLEDB connection string. The connection string provides driver, server, username, password and other important information to the OLEDB subsystem so that the connection can be made. The easiest way to generate a proper connection string is by pressing the New button on the visual step editor. The connection string is automatically encrypted when adding the step in Visual Mode.

Stored Procedure

Text, Required
MARKUP: PROCEDURE="sp_delete_activity"

Name of the dataset that should be created and populated with the results (if any) of the SQL Statement upon execution. To access the data in subsequent steps simply specify %DATASETNAME.FIELDNAME% inside a Loop Dataset action (to recurse the rows).

Create and Populate Dataset

Text, Required
MARKUP: RESULTDATASET="DATESETNAME"

Name of the dataset that should be created and populated with the results (if any) of the SQL Statement upon execution. To access the data in subsequent steps simply specify %DATASETNAME.FIELDNAME% inside a Loop Dataset action (to recurse the rows).

Time Out After

Number, Optional Default - 0
MARKUP: TIMEOUT="500"

Specifies the amount of seconds that the query should be allowed before timing out at the server level.

Prompt User for Name and Password

Yes/No, Optional - Default - NO
MARKUP: LOGIN="YES"

Specifies that the user should be prompted with a username and password for the database server each time the task is run.

Notes

A variable field Dataset is generated

A dataset is a multiple column, multiple row container object. This action creates and populates a dataset, the fields contained within that dataset are determined by the query that was executed. For example if a stored procedure containing the following query is executed:

SELECT firstname, lastname, company from customer where city='Los Angeles';

Then the following data set would be generated:

datasetname
|--firstname
|--lastname
|--company

Common Column Name Description

Along with the standard fields included, every dataset contains common fields. The table below describes the common set of columns that a dataset creates (assuming the dataset name assigned was theDataset).

Name

Data Type

Return Value

theDataset.CurrentRow

Number

The current row that will be accessed in the dataset by an expression that does not contain a specific row index.

theDataset.TotalRows

Number

The total number of rows in the dataset

theDataset.TotalColumns

Number

The total number of columns (not including the static columns) in the dataset.

theDataset.ExecutionDate
 

Date

The date and time the dataset was created and populated

theDataset.RowsAffected
 

Number

The number of rows affected by an update.

theDataset.SQLQuery
 

Text

The SQL Query that was used to generate this dataset (If a SQL Query was not used, this value is empty).

theDataset.Datasource
 

Text

The datasource used for the SQL Query, if applicable.

theDataset.ColumnNames
 

Text

A comma-delimited list of the column names in the dataset

 

A record (row) is created for each record (row) that is retrieved from the server. To access this data use the Loop Dataset Action to loop through the records, inside the loop you can extract the data from the field of your choice (from the current record) by using an embedded expression such as the one that follows:

%mydatasetname.firstname%

or you could combine two fields together like this:

%mydatasetname.firstname + " " + mydatasetname.lastname%

Embedded Expressions such as these can be used in any parameter in any action. So, to display the data in a message box the AML code would look like this:

 <AMMESSAGEBOX MESSAGETEXT="%mydatasetname.firstname%" WINDOWTITLE="The firstname of the current record is">

At runtime the text %mydatasetname.firstname% is replaced by the contents of the subject of the current record.

More on datasets

Custom Description

This action includes the Description tab for entering a custom step description.

More on setting custom step description

Standard Error Handling Options

This action also includes the standard Error Causes and On Error failure handling options/tabs.

More on Error Handling Options

Variables and Expressions

All text fields allow the use of expressions, which can be entered by surrounding the expression in percentage signs (example: %MYVARIABLE%, %Left('Text',2)%). To help construct these expressions, you can open Expression Builder from these fields by pressing F2.

More on variables
More on expressions

More on the expression builder

Example

NOTE: The code below can be copied and pasted directly into the Steps pane of the Task Builder.

<AMSQLSTOREDPROC CONNECTIONSTRING="Provider=SQLOLEDB.1;Password=password;Persist Security Info=True;User ID=username;Initial Catalog=Testing;Data Source=server" PROCEDURE="sp_delete_activity" NAMES="RETURN_VALUE,@activityid" VARIABLES="&quot;&quot;,&quot;&quot;" TYPES="Integer,WideString" DIRECTIONS="ReturnValue,Input" PRECISIONS="10,0" NUMSCALES="0,0" SIZES="0,15" ATTRIBUTES="&quot;&quot;,Null">