Skip to content
+1-888-319-3663

COMMUNITY FORUM

Stored Procedure Task

Michael Beaupre asked 6 years ago
At the successful conclusion of my SmartConnect Map, I want to run a stored procedure.  It appears that a stored procedure is not allowed as a Data Source in a Map, and so I’m attempting to run the stored procedure as a Task when my Map finishes successfully.
I’m defining my Task like this:
     [dbo].[CNX_PRC_IF_CIS_WAMS_OL_AUDIT3] @oErrorState integer output, @oErrorString varchar(255) output;
 
but I’m getting this error: “Must declare the scalar variable @oErrorState”.
My “Audit3” Stored Procedure has only two parameters, both of which are output parameters:  @oErrorState and @oErrorString
Please advise.
 
Gracias.
 
 
Answers
Chris Hanson Staff answered 6 years ago
Michael,
You need to declare your SQL parameters before using them in the call to your stored proc. So in the map create the Run SQL post map task, and in the section for the SQL code you would have something like the following:
 
DECLARE @oErrorState Integer
DECLARE @oErrorString varchar(255)
EXEC CNX_PRC_IF_CIS_WAMS_OL_AUDIT3 @oErrorState output, @oErrorString output
 
Stored procedures can be used in a data source of a map as well if you choose the ODBC or OLEDB connections and point them to your SQL Server. They only really work as a source if they return a dataset however.
H

If you would like to submit an answer or comment, please sign in to the eOne portal.