error when using stored procedure

balajimadhav · Post by **balajimadhav** » Tue Jul 18, 2006 11:43 am

Hi ,
i am getting the error below when calling a stored procedure in which parameters are passed through a transformer.

<<job_name>>.<stored procedure name>: Must have an output link to declare this type of parameter(B or O).

thanks in advance.

DSguru2B · Post by **DSguru2B** » Tue Jul 18, 2006 12:27 pm

Need more info,
HOw are you calling the stored procedure, STP stage of odbc stage?
What type of parameter are you specifying while importing the stored procedure?

ray.wurlod · Post by **ray.wurlod** » Tue Jul 18, 2006 6:19 pm

It is not possible to call a stored procedure from a Transformer stage (other than through a custom Routine, and even there it is not a good idea). So you don't understand completely what your job design is actually doing.

A passive stage (for example ODBC or Stored Procedure stage) exposes the methods for calling the stored procedure, and the Transformer stage executable invokes those methods.

An ODBC stage, or the per-row section of a Stored Procedure stage, can only invoke a stored procedure that deals with a record set. So, if the stage is "reading" data, then the SP must supply data. If the stage is "writing" data, then the SP must consume data. And the number of arguments (parameters) in the SP must match the number and type of columns in the job design.