One Star

Problem getting return value from MSSQL function

I am using a tMSSqlSP component to call a function, pass a value, and get a return value. When I run it I get the error: "java.sql.SQLException: Parameter '2' not registered as output parameter. Call registerOutParameter()." I tried unchecking the Is Function box and adding it as an output parameter but unfortunately I get "java.sql.SQLException: Procedure or function GetBACForDealerCode has too many arguments specified." I ran profiler to see how it was calling and the function option seems to call it correctly.
Any ideas? Thanks!
7 REPLIES
One Star

Re: Problem getting return value from MSSQL function

Hi
Welcome to Talend Community!
You'd better add a column in 'Schema Column' and select 'Out' type for it.
Regards,
Pedro
One Star

Re: Problem getting return value from MSSQL function

Hi
Welcome to Talend Community!
You'd better add a column in 'Schema Column' and select 'Out' type for it.
Regards,
Pedro

If I do that it errors with "java.sql.SQLException: Procedure or function GetBACForDealerCode has too many arguments specified"
One Star

Re: Problem getting return value from MSSQL function

Hi
Please show me the arguments of this procedure or function.
When selecting 'is function', you have to select output column from 'Return result in'.
When not selecting it, you need to add 'OUT' column in 'Parameters'.
Regards,
Pedro
One Star

Re: Problem getting return value from MSSQL function

Attached is a screenshot - including the SQL behind the function. This example gives the error "java.sql.SQLException: Parameter '2' not registered as output parameter. Call registerOutParameter()."
One Star

Re: Problem getting return value from MSSQL function

Hi
After creating this function in my local MSSQL server, I know why you didn't get return value.
Modify the settings of tMSSqlSP as the following images.
Regards,
Pedro
One Star

Re: Problem getting return value from MSSQL function

While that did work it doesn't seem like that should be correct - if I needed to keep passing the DealerCode variable through it would get overwritten in that scenario. Is that the intended behavior?
Thanks!
One Star

Re: Problem getting return value from MSSQL function

Hi
Got you.
The schema should be like this.
BAC
DealerCode
The 'BAC' column should be before the 'DealerCode' column.
Regards,
Pedro