Stored Proc running thru webmethods not updating the status of a particular field.

[ganesh]

Hi,

I have some strange issue.The thing is If I run the stored procedure thru webmethods the status of a particular field is not updating sometimes.and sometimes its updating.I did R&D for the last two days but unable to figure out the rootcause.I dont know whether its a patch issue or any fix or updates.I went thru the advantage but no result.
For example if I submit some 1000 records(trades) the status is updated for 900 and the for remaining the status its not updating in the database.
Did any one face this type of problem?Any help or ideas regarding will be greatly appreaciated.
I also went thru the stored proc and analysed some info like
Where is the transaction handling done?
TX Mgmt Done in the sproc
Are there any dependencies on the updated fields?
There was a trigger that was removed in uat
Is there a pattern around the failure?
No specific pattern found
Does it occur only for specific items or is it more random?
It’s random , no specific pattern found

Any help will be helpful.

Thanks & Regards
Ganesh

[mcarlson]

What happens if you submit the *same* 1000 trades from outside of webMethods? My guess is that there is either a bug in the SP or that there is something different about data data in the 100 transactions that are not processing correctly. That data issue could be originating in the Flow service calling the JDBC Adapter Service.

Are you calling the SP 1000 times to process 1000 transactions or are you calling it once and passing data for all 1000 transactions?

Mark

[ganesh]

Thanks for your response
If the trades submitted outside of webmethods its working fine.
And also I am calling the SP 1000 times for 1000 transactions.
It has been a major issue in the production, as of now they are changing the status manually.

Regards
Ganesh

[mcarlson]

Not much else we can do here since we are unfamiliar with the logic in the stored procedure. Debug the SP to find out why the "status" field might not be updated. Once you understand why that might be, try to recreate it using a test transaction with the condition that would cause that.

BTW, what happens if you send the same "trade" transaction 1000 times. Given the same data the stored proc should work the same way.

Did I mention that webMethods is not a product. What product and version are you using to invoke the SP? I have assumed you were using the IS JDBC Adapter.

Mark

[manishjain]

Hi
I m facing somewhat same problem, I m invoking a stored procedure thru the connection having Transaction Type as LOCAL_TRANSACTION.
It is returning correct parameters what I m expecting but the records which should get created in the table on execution of this proc...are not in the table.
I tried to use explicit commit services also but no no luck.
can anyone suggest whether it is a problem at wM side of the transaction...or stored procedure?
Thanks