SQL Query

Passes an SQL (Structured Query Language) statement including queries to the data source specified via OLEDB. If a query is specified, a Dataset with the name you indicate is created and populated with the query results. Useful for automated retrieval, update and management of data. Could also be used to test database response times and size quotas. Given that this action provides the full power of SQL, which is a powerful database language itself - the possibilities are limitless. A working knowledge of SQL will help get the most out of this action.

Declaration

<AMSQLQUERY CONNECTIONSTRING="text" RESULTDATASET="text" TIMEOUT="number" MEASURE="text(options)">SELECT firstname, lastname from customer where city='San Diego'</AMSQLQUERY>

Example

This example demonstrates using SQL query with a Loop Dataset action. It retrieves all customers where the 'City' field is 'San Diego' and populates a dataset with the results. A Loop Dataset action is used to loop through the rows of the dataset. During each iteration, a message box appears showing the current result. To make this task work, change the SQL Query connection string and query to match your database.

<AMSQLQUERY CONNECTIONSTRING="AM2y0llIsSVZ0/xcPsmhQdF/yyyXTZF+2yHRFF0mXdC0/BfctA==aME" RESULTDATASET="theDataset" TIMEOUT="2" MEASURE="minutes">SELECT firstname, lastname from customer where city='San Diego'</AMSQLQUERY>

<AMLOOP TYPE="DATASET" DATASET="theDataset">

<AMSHOWDIALOG>First Name - %theDataset.firstname%

Last Name - %theDataset.lastname%

</AMSHOWDIALOG>

</AMLOOP>

See Also: Open SQL Connection, Close SQL Connection, Stored Procedure, Loop Dataset, Data Link Properties

Because the results retrieved may contain multiple records (rows) and multiple fields (columns) - to access data retrieved from SQL Query you must use the Loop Dataset action to iterate the records and embedded expressions in order to extract the data from the individual fields (i.e. %datasetname.fieldname%). See notes below for more information.

General Tab

Property

Type

Required

Default

Markup

Description

Connection String Type

       

Indicates where this action's connection string should originate from. The connection string includes the source database name, driver, username, password and other properties needed to establish the initial connection. Different parameters are available depending on the option selected. The available options are:

  • Use custom connection string (Default) - Specifies that a custom OLEDB connection string will be set for this action. Select this option if performing a single query.

  • Use existing SQL connection from a session - Specifies that a connection string should derive from a session created by a previous Open SQL Connection step. This allows several queries to be linked to a specific session.

  • Use pre-defined connection string - Specifies that the connection should derive from a variable or constant.

NOTE: This is a visual mode parameter only used during design time. It contains no markup.

Connection String

Text

Yes if connection type is custom

(Empty)

a)CONNECTIONSTRING=

"AM1NGBAj8FWs8hn9B2/DA0A"

b)CONNECTIONSTRING="%varName&"

Specifies an OLEDB connection string. Click New in the visual step editor to open the Data Link Properties Wizard allowing you to setup a custom connection string. Valid only if Use custom connection string is selected in the Connection String Type parameter. For more details, see Data_Link_Properties_Wizard.

Session Name

Text

Yes if connection is from a session

SQLSession1

SESSION="Session10"

Indicates the session name that this connection should derive from. Valid only if Use existing SQL connection from a session is selected in the Connection String Type parameter.

Pre-defined Connection String

Text

Yes if connection is pre-defined

(Empty)

a)PREDEFINEDCONNECTION=

"%varName%"

b)PREDEFINEDCONNECTION=

"AM1NGBAj8FWs8hn9B2/DA0A"

Indicates the pre-defined connection string that should be used. Valid only if Use pre-defined connection string is selected in the Connection String Type parameter.

Prompt user for a name and password

Yes/No

No

No

LOGIN="YES"

Specifies that the user should be prompted to enter a username and password for the database server each time the task is run. Valid only if Use custom connection string or Use pre-defined connection string is selected in the Connection String Type parameter.

Create and populate dataset

Text

Yes

(Empty)

RESULTDATASET="theDataset"

The 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 specify %datasetname.fieldname% (where datasetname specifies the name of the dataset and fieldname specifies the name of the dataset field) inside a Loop Dataset action (to recurse the rows).

More details regarding datasets

SQL Statement

Text

Yes

(Empty)

SQLSTATEMENT="SELECT * FROM CUSTOMER WHERE CITY='Los Angeles'"

Specifies the SQL statement that should be executed.

Cache Tab

Property

Type

Required

Default

Markup

Description

Use cached query data if execution date is after

Date

No

(Empty)

CACHEDAFTER="%DateSerial(2010,06,14) + TimeSerial(11,41,44)%"

If enabled, specifies a date/time after which the query should use cached data instead of contacting the database. Usually this would be a calculated value using an expression containing DateAdd.

The default option is Never use cached data which specifies that cached data will not be used for this connection.

Advanced Tab

Property

Type

Required

Default

Markup

Description

Maximum number of rows returned (optional)

Number

No

(Empty)

MAXROWS="500"

The maximum number of rows that should be returned by the database server.

The query times out after (optional)

Number

No

(Empty)

TIMEOUT="2"

The amount of time that the query should be allowed before timing out at the server level.

Measure

Text (options)

No

Seconds

MEASURE="minutes"

Enter a value and select from the following time intervals:

  • Milliseconds

  • Seconds (default)

  • Minutes

  • Hours

Description Tab

The Description tab allows you to customize the text description of any step as it appears in the Task Builder's Steps Pane.

More on setting custom step description

Error Causes Tab

The Error Causes tab allows you to select/omit specific errors that should cause a particular step to fail.

More on Error Causes properties

On Error Tab

The On Error tab allows you to determine what the task should do if a particular step encounters an error.

More about On Error properties

Variables and Expressions

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

More on variables

More on expressions

More on the Expression Builder