Currently, i m also facing the same problem in my application. Following is the scenario.
The application is related to TMS domain. It contains shipments, purchase orders like that. Shipment contians number of Shipment Orders.
When i open a Shipment, it is going to show around 600 shipment orders related to that shipment. For that i wrote a stored procedure, which will return back to my java class as 30 resultsets. Each resultset contians many rows. For example, for 600 shipment orders shipment, the stored procedure is returning around 11800 records in 30 resultsets.
After that fetch those records, i m showing in the UI.
This is sometimes working and sometimes it is not working. It is mentioning like that SQL STATE IS null or 0. If i use the DBCC dropcleanbuffers COMMAND ON DB SIDE, it is fetching and showing records properly.
Can you please let me know, y? it is showing proper results after clearing the buffers in db. otherwise it is throwing an error?
The same procedure will cal daily around 1000 times. I think, it is performance effect. But why it is showing records aftr clear the buffers.
Thanks for your help....