The Stored Procedure action is used to execute a stored procedure on a database via OLE DB.
NOTE: Some knowledge of SQL and stored procedures is necessary to set properties for this action.
From the Task Builder Available Actions pane, open the Database folder and double-click the Stored Procedure 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.
In the Stored procedure box, select the stored procedure.
To enter parameters for the stored procedure, click
the Add button and enter the parameters
in the Stored Procedure Parameters
After a parameter is entered, it is listed in the Parameters box in the Stored Procedure properties. You can add as many parameters as needed.
When a parameter is selected on the list, the details are shown on the right. To edit or delete any parameter, select it on the list and click Edit or Delete.
Click the Advanced tab, and enter the properties as follows:
In the Stored procedure times out after box, enter the maximum number of seconds you want AutoMate to wait for the stored procedure to be executed. If it is not complete after that time, the step will fail.
If the stored procedure includes a query, go to the Create and populate dataset box and enter a name for the dataset to populate with the query result. (Dataset names must be alphanumeric, begin with a letter, and contain no spaces.)
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 Stored Procedure Action