Stored Proc. error Invalid Cursor state

srikanthd1978 · Post by **srikanthd1978** » Tue Jan 02, 2007 9:22 pm

hi all,

I am trying to execute a Stored Proc using a SP stage/operator in a PX Job.

My Job Design is ..Seq File -> Transformer -> SP Stage -> Seq file.

The Source sequential file contains input to the SP. In the XFM i hard code the 2 output params that i have to use to call the stored proc..

When i pass one record, the SP gets executed successfully..

However when i pass multiple records and check the option ( Execute SP for each row )..my job aborts with the following error..

Fatal Error: Fatal: [IBM][CLI Driver] CLI0115E Invalid cursor state. SQLSTATE=24000

has anyone encountered this issue...when i dug more into this error..i have reasons like .."the Cursor is already opened or the Cursor is not opened.."...

Is this an issue with the Stored Procedure itself or something to do with the SP opertor/stage in datastage..

( We should be able to pass multiple records as inputs and should be able to get results from the Stored proc )..i dont know why this error appears..

all help is appreciated..

Thanks

kumar_s · Post by **kumar_s** » Wed Jan 03, 2007 3:28 am

Try changing array size and transcation size to 1. So that you can force record to flow one after other, rather that caching it up.

srikanthd1978 · Post by **srikanthd1978** » Wed Jan 03, 2007 8:41 am

Thanks Kumar for the quick response...

I tried setting the Transaction Size = 1 ( could not find the option for array Size).. when i run the Job i get the following error and the job aborts...

Stored_Procedure_14: [IBM][CLI Driver] CLI0125E Function sequence error. SQLSTATE=HY010
[IBM][CLI Driver] CLI0125E Function sequence error. SQLSTATE=HY010

any ideas ??