topic Re: Problem getting return value from MSSQL function in Talend Studio

Problem getting return value from MSSQL function

Anonymous — Thu, 24 May 2012 19:45:50 GMT

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!

Re: Problem getting return value from MSSQL function

Anonymous — Fri, 25 May 2012 02:44:45 GMT

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

Re: Problem getting return value from MSSQL function

Anonymous — Fri, 25 May 2012 14:27:22 GMT

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"

Re: Problem getting return value from MSSQL function

Anonymous — Mon, 28 May 2012 02:59:12 GMT

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

Re: Problem getting return value from MSSQL function

Anonymous — Tue, 29 May 2012 21:53:53 GMT

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()."

Re: Problem getting return value from MSSQL function

Anonymous — Wed, 30 May 2012 03:18:53 GMT

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

Re: Problem getting return value from MSSQL function

Anonymous — Wed, 30 May 2012 16:27:28 GMT

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!

Re: Problem getting return value from MSSQL function

Anonymous — Thu, 31 May 2012 03:45:53 GMT

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

Re: Problem getting return value from MSSQL function

Anonymous — Tue, 03 Jul 2018 17:22:42 GMT

I don't see an image in this message. Can you paste again the image or write in text which setting need to be changed.