long running SQL CURSOR

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

, +

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)

Posted 20-Dec-11 20:04pm

ArunBose

Updated 1-Jan-12 20:53pm

v4

Add a Solution

Comments

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

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Al Moje · Answer 1 · 2011-12-20T20:14:00

Solution 1

Hi,

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

Regards,

Posted 20-Dec-11 20:14pm

Al Moje

Wendelius · Answer 2 · 2011-12-21T11:01:00

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: http://msdn.microsoft.com/en-us/library/ms175518.aspx[^]

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

**AmitGajjar** · Answer 3 · 2012-01-01T21:40:00

Hi,

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

thanks
-amit.

long running SQL CURSOR

3 solutions

Solution 1

Solution 3

Solution 4

Add your solution here

Preview 0