Page 1 of 1
Reg odbc stored procedure
Posted: Thu Apr 16, 2009 8:51 am
by dineshrk
We are getting warning while accessing a stored procedure, which takes a single parameter from a sequential file and returns a result set of 3 columns. we need to know how to pass parameter value by taking from a file to the ODBC stage to trigger SP and capture the result set in a file.
Posted: Thu Apr 16, 2009 9:08 am
by chulett
Warning? What warning?
Reg odbc stored procedure
Posted: Thu Apr 16, 2009 11:24 am
by dineshrk
[quote="chulett"]Warning? What warning?[/quote
The warning is
"
CopyOfTEST_SP..Transformer_20.Ref_data: DSD.BCIPut call to SQLExecute failed.
SQL statement:{call IntlHeadCount.dbo.sp_user_scope_ifeed(?)}
SQLSTATE=24000, DBMS.CODE=0
[DataStage][SQL Client][ODBC][DataDirect][ODBC SQL Server Driver]Invalid cursor state
AG_USER_ID = "utester1124""
Pls let me know if any furher input is needed
Posted: Thu Apr 16, 2009 4:11 pm
by chulett
Are you certain you've got a valid / working stored procedure? Tested it outside of DataStage?
Posted: Sun Apr 19, 2009 11:08 pm
by dineshrk
chulett wrote:Are you certain you've got a valid / working stored procedure? Tested it outside of DataStage? ...
It is a valid working one and it returns a result set in the Query Analyzer.
Posted: Mon Apr 20, 2009 7:11 am
by chulett
OK. Have you tried using the Stored Procedure stage rather than ODBC? Also, read the docs on the SP stage for the rules it needs to follow to work with DataStage.