The SQL Query action is used to send a SQL statement to a database via OLE DB. The statement can be a query or any other type of SQL statement. If it is a query, AutoMate populates a dataset with the query results. To use the results of a query, create an additional step using the Loop Dataset action to retrieve the data from the dataset.
Some knowledge of SQL is necessary to set properties for this action.
From the Task Builder Available Actions pane, open the Database folder and double-click the SQL Query action or drag it into the Steps pane.
In the Connection string type box, select from the drop-down list. Then enter the properties for your selection:
For a custom connection string, do one of the following:
Type or paste the connection string into the Connection string box.
Click in the Connection string box, and then click the Insert Expression/Variable button and use the Expression Builder to enter the connection string.
If you have the connection string stored in a data link file, click the Open Folder button , navigate to the file, and click Open.
Click the New button and use the Data Link Properties Wizard to enter the connection string.
For an existing SQL connection from a session, select the session name in the Session name box.
For a pre-defined connection string, select the connection name in the Pre-defined connection string box. (Pre-defined connection strings are set up through the Open SQL Connection action.)
If you want the user name and password for the database server to be required every time the step runs, select Prompt user for a name and password.
If you are setting up a query: in the Create and populate dataset box, enter a name for the dataset to be populated with the query results. (Dataset names must be alphanumeric, begin with a letter, and contain no spaces.)
In the SQL statement box, enter the SQL statement to use. You can enter multiple statements separated by semicolons. If more than one statement returns data to a dataset, the first will be the one recorded.
If you want to enable the use of cached data, click the Cache tab, and select Used cached query data if execution date is after. Then use the date and time boxes to select a date and time. You can also click the Custom button and select a limiter such as Last 24 hours, or you can select Expression and enter an expression.
Click the Advanced tab, and enter the properties as follows:
In the Maximum number of rows returned box, enter the maximum number of records to be returned in the query result. Any additional records that match the query are truncated. If this is left blank, all matching records are returned.
the Query times out after box,
enter the maximum number of seconds you want AutoMate to wait for the
statement to be executed. If the statement or query is not complete after
that time, the step will fail.
If no selection is made the system default time-out (30 seconds) is used.
To set the Description tab properties, refer to Description Tab Options.
To set the Error Causes and On Error tab properties, refer to Step Error Handling.
When finished, click OK to save settings and close the properties dialog window.
Adding Task Steps
Execute SQL Query Action