Click here to Skip to main content
15,569,840 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I have a Stored Procedure with CURSOR which takes around 3 minutes to run. It works fine in the Management Studio, but not in my Application.I have increased its command timeout to 600. Now, Sometimes I gets full results into the Application(When I am executing the procedure 4 or 5 times)

Can any one help me to retrieve full result on first time execution itself....

Now I removed all cursors from the query and reduced the execution time to "20 secs or less ". But Still the problem exists. The result is not complete, many rows are removed from my result.(I gets only 11965 records)
Updated 1-Jan-12 21:53pm
Al Moje 21-Dec-11 2:07am    
Did you increased also your connection timeout?... If not then do it...
thatraja 21-Dec-11 3:28am    
Show the SQL script


if your cursor is Forward_Only(least restrictive) then you need to check something else. please let us know what you have written in your cursor,

Please check Cursor Type

and check which type of cursor is best suite in your logic.

hope using proper type you can solve your problem

Share this answer
The problem may be insufficient indexing, possibly locking issues, badly formed SQL etc.

Try to use SSMS Activity monitor to see that no locks interfere:[^]

And optimize the query and/or the underlying tables/indexes. Without seeing the statements, there's not much to help with concerning the optimization.
Share this answer

See this link if could help...
How set CommandTimeout and ConnectionTimeout

Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900