A MSSQL stored procedure destination will allow data source fields to be mapped to MSSQL stored procedures, and the stored procedures triggered via map processing.
To add an MSSQL stored procedure destination to a map:
| 1. | Select create, or select the Maps Quick Link and double click on the map to be edited. |
| 2. | If creating a new map enter the map id and description. |
| 3. | If creating a new map select a valid data source and map key fields. |
| 4. | Select Microsoft SQL Stored Procedure as the destination. |
| 5. | Select a Connection from the drop down list. If Custom Connection is selected the server, authentication details and database selection will be required. If a default connection is selected the server, authentication details and database selections will be grayed out and defaulted from the selected connection. If a new default connection should be set select the manage default connections button and enter the new details. |
| 6. | Enter the name of the MSSQL Server / Instance to be used for this data source. This may be defaulted by the connection selected in step 5. |
| 7. | Select the required authentication type. Windows authentication will use the details for the current windows user to try to connect to the MSSQL server. Selecting SQL Server Authentication will required the entry of a MSSQL username and password. This setting may be defaulted by the connection selected in step 5. |
| 8. | If required enter the MSSQL login username. This may be defaulted by the connection selected in step 5. |
| 9. | If required enter the MSSQL password. This may be defaulted by the connection selected in step 5. |
| 10. | Select the Connect button  to use the entered details to connect to the MSSQL server. If the connection is successful available databases will be displayed in the database drop-down list. |
| 11. | If required select the MSSQL database from the drop-down list. This selection may be defaulted by the connection selected in step 5. |
| 12. | Select the required stored procedure from the drop-down list. |
| 13. | If this is an eConnect stored procedure with validation messages stored in the eConnect tables check the eConnect Procedure checkbox. |
| 14. | Double click the stored procedure in the mapping grid and map the data source columns to the destination stored procedure parameters. |
| 14. | Select OK to close the mapping window. |
| 15. | Select Save to save your changes. |
Notes:
| • | For a stored procedure to be used by SmartConnect it must have at least two output parameters, and integer and a varchar. |
| • | The first integer output parameter is checked to determine the success of the call. If the integer is 0 the process is considered successful. Anything else and the process is considered to have failed. |
| • | The first varchar output parameter is checked to determine any error messages. This message will be displayed to the SmartConnect user if the stored procedure is deemed to have failed. Note: if this is an eConnect type store procedure with the validation messages stored in the eConnect tables this parameter should return the number of the eConnect message to be displayed as a varchar. |