Reg odbc stored procedure

dineshrk · Post by **dineshrk** » Thu Apr 16, 2009 8:51 am

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.

chulett · Post by **chulett** » Thu Apr 16, 2009 9:08 am

Warning? What warning?

dineshrk · Post by **dineshrk** » Thu Apr 16, 2009 11:24 am

[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

chulett · Post by **chulett** » Thu Apr 16, 2009 4:11 pm

Are you certain you've got a valid / working stored procedure? Tested it outside of DataStage?

dineshrk · Post by **dineshrk** » Sun Apr 19, 2009 11:08 pm

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.

chulett · Post by **chulett** » Mon Apr 20, 2009 7:11 am

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.