How to retreive data from Stored Procedures in SQL server into Statistica?
Article ID: KB0080965
Updated On:
| Products | Versions |
|---|---|
| Spotfire Statistica | 12.0 and later versions |
Description
Statistica has the ability to execute Stored procedure with parameters in SQL server and bring back results to Statistica. This article provides details of the same.
PREREQUISITES:
PREREQUISITES:
1. Data Admin (DADM) permissions in Enterprise Manager
2. Details of existing Stored procedure in SQL Server
Issue/Introduction
Statistica has the ability to execute Stored procedure with parameters in SQL server and bring back results to Statistica. This article provides details of the same.
Resolution
For the purpose of this article, we will assume the following simple stored procedure has been created in SQL server with name spSel and a parameter @Cat to filter records from a table.
1. Login to Enterprise Manager and Create a database connection in Enterprise Manager to connect to the database with the stored procedure ( if one doesn't exist already). Seek assistance from a user with Enterprise manager permissions System Admin (SADM) or Database Admin(EXTDB_ADM), if necessary.
2. Right click on System View folder of choice and select "New Data Configuration".
3. Name the data configuration appropriately and pick the database connection from Step 1 that connects to the database with Stored procedure. Optionally you may add a description for the data configuration. Click on New next to the Queries in the data configuration.
4. In the SQL statement section type : exec [StoredProcedurename] . If the stored procedure does not have any parameters, this will work. If you know the pameter name, you can type the parameter in the SQL Statement section as well. exec [StoredProcedurename] @param= 'value' or if you are not sure of the name of your parameters you may type exec [StoredProcedurename] ? . Click on Evaluate SQL to validate the statement and retrieve columns.
5. Define Access Permissions for the data configuration and click on Commit on Top left corner of Enterprise manager to save the data configuration.
6. Explore the data configuration and the user will be prompted for parameters. Double click on parameter or click on Edit to add a value to the parameter.
7. Click OK and Finish to retrieve data into Statistica.
NOTE: The parameters may be preemptively defined before Run by clicking on Parameters in the data configuration and adding a value for the parameter.
or you may alternatively define the SQL statement with parameters as you would do in SQL server to pass parameters as shown below :
1. Login to Enterprise Manager and Create a database connection in Enterprise Manager to connect to the database with the stored procedure ( if one doesn't exist already). Seek assistance from a user with Enterprise manager permissions System Admin (SADM) or Database Admin(EXTDB_ADM), if necessary.
2. Right click on System View folder of choice and select "New Data Configuration".
3. Name the data configuration appropriately and pick the database connection from Step 1 that connects to the database with Stored procedure. Optionally you may add a description for the data configuration. Click on New next to the Queries in the data configuration.
4. In the SQL statement section type : exec [StoredProcedurename] . If the stored procedure does not have any parameters, this will work. If you know the pameter name, you can type the parameter in the SQL Statement section as well. exec [StoredProcedurename] @param= 'value' or if you are not sure of the name of your parameters you may type exec [StoredProcedurename] ? . Click on Evaluate SQL to validate the statement and retrieve columns.
5. Define Access Permissions for the data configuration and click on Commit on Top left corner of Enterprise manager to save the data configuration.
6. Explore the data configuration and the user will be prompted for parameters. Double click on parameter or click on Edit to add a value to the parameter.
7. Click OK and Finish to retrieve data into Statistica.
NOTE: The parameters may be preemptively defined before Run by clicking on Parameters in the data configuration and adding a value for the parameter.
or you may alternatively define the SQL statement with parameters as you would do in SQL server to pass parameters as shown below :
Feedback
Yes
No
Powered by
