Capture Stored procedure Retrun value

[myukassign]

I have a requirement to call a stored procedure from ODBC stage and capture the return value.

The issue is , I don't know what should be the name of the return value, I tried all possible things but it gives me the binding error.

"##W TFOR 000000 05:00:06(014) <ODBC_Enterprise_20> When checking operator: The modify operator has a binding for the non-existent output field "return_value".
##W TFOR 000000 05:00:06(015) <ODBC_Enterprise_20> When checking operator: The modify operator keeps field "return_value" which is not present in the output int"


Steps I am doing

1. My stored procedure looks like this.

create procedure GetNewSeqVal_SEQUENCE
as
begin
declare @NewSeqValue int
set NOCOUNT ON
insert into SEQUNCE (SeqVal) values ('anyval')

set @NewSeqValue = scope_identity()

return @NewSeqValue
end

2. Created the following canvas, ODBC stage -> File stage . In odbc stage user defined SQL I gave "execute GetNewSeqVal_SEQUENCE"

3. The column defnion of the odbc stage I tried with just one column "RETURN_VALUE" . But it gives me the binding error. I know very well that, the return value column name of my stored procedure and the column name I decalred is not matching. I also tried the names like "NewSeqValue"

Kindly request anyone to help me here, what should I given as the column name or my mistake is something else.

* I test the stored proc separatly and its working and its a SQL server Store proc.

[myukassign]

Is it possible to call a stored procedure from odbc stage and capture its return value in datastage?

Please help

[chulett]

Call? Yes. Capture? No. As far as I know, you'd need to use the actual Stored Procedure stage for that functionality.