Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Cursor fetch loops endlessly


Cursor fetch loops endlessly

Author
Message
mwitthoft
mwitthoft
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 19
<< not enough to try to help >>
As the original poster pointed out, the code is good in the first place so even if I send you the whole application it'll probably run correctly.

I don't need you to read my code. I need to find somebody who has encountered the same symptom.

<< re-code without cursor >>
will probably get me out of the current jam, but still leave me wondering when and where cursors will randomly restart themselves.
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24259 Visits: 37978
If you are routinely using cursors in your production code you are more than likely doing things wrong. Cursors should be avoided for most database activity. The only time I have used cursors has been for maintenance routines or one off processes, and even then I try to limit them as much as possible.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16636 Visits: 17024
mwitthoft (1/10/2014)
but still leave me wondering when and where cursors will randomly restart themselves.


If you don't use them then there really isn't much need to have any concern here. :-)

Cursors do not just randomly restart themselves. I am not a fan of cursors to say the least but I have written lots of them over the last couple decades (very few in recent years). I have occasionally experienced some strange and quirky behavior with cursors. It always comes back to some strange bug in the code.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
mwitthoft
mwitthoft
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 19
<<strange bug in the >>
Strange indeed, when I can back up the database from one server, restore it onto another server, and the behavior of the stored proc/cursor is different in the two places. That makes me say "SQL Server version and/or configuration issue".

I have seen as many comically inappropriate cursors as anybody, and only use them when they make sense: for example, when calling an API proc that is not mine.

Cursor thru InputTable
Fetch next into @MyVar
while MoreData
Exec API_proc @MyVar
Fetch next into @MyVar
wend
LutzM
LutzM
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7009 Visits: 13559
Just a probably totally silly question:
What is the CURSOR_DEFAULT database setting for the DB in question?

Reason for asking:
If it's set to GLOBAL and you're not specifically declare the c.u.r.s.o.r. *cough* as LOCAL, then you might run into an issue where a separate code block that's using the same connection references the cursor name without specifically declare it within it's scope (see this link).

You could try to add the LOCAL option just to see if it's changing the behavior.
The next one you could try is to add the STATIC option which would prevent the cursor being influenced by any changes made to the source table.

You could also use sp_cursor_list to get the cursor_handle(s) visible to the sproc. You might even want to write that stuff to a separate table for further analysis.

But the most important message is: all I recommended before is to understand WHY it occurs. You should still convert the code to a set base solution!



Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
mwitthoft
mwitthoft
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 19
Good ideas about the cursor declaration. Yes, it's declared LOCAL. And although the proc does not modify the table that the cursor is reading, I'm planning to try a superfluous STATIC declaration when I get back in the office, just to see if that flushes the problem.

I'm curious how you recode this logic to a set-based approach:
Cursor thru InputTable
Fetch next into @MyVar
while MoreData
Exec API_proc @MyVar
Fetch next into @MyVar
wend

Of course I can build my own cursor:
Instead of.....Declare Cursor for Select xyz............. I can say.....Select Xyz into #Temp..........
Then instead of .........Fetch/Process/FetchNext.......... I can say ....... select top 1 from #Temp/process/Delete (that row) from #Temp/Select Top1 from #Temp
which is technically set-based but probably not what you meant.
LutzM
LutzM
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7009 Visits: 13559
The STATIC option basically does exactly what you described as "your own cursor": it makes a copy of the cursor definition in tempdb

The set based solution we're talking about would be a replacement of the API_proc.

In order to help you further we'd need to know what that proc does.



Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24259 Visits: 37978
mwitthoft (1/12/2014)
Good ideas about the cursor declaration. Yes, it's declared LOCAL. And although the proc does not modify the table that the cursor is reading, I'm planning to try a superfluous STATIC declaration when I get back in the office, just to see if that flushes the problem.

I'm curious how you recode this logic to a set-based approach:
Cursor thru InputTable
Fetch next into @MyVar
while MoreData
Exec API_proc @MyVar
Fetch next into @MyVar
wend

Of course I can build my own cursor:
Instead of.....Declare Cursor for Select xyz............. I can say.....Select Xyz into #Temp..........
Then instead of .........Fetch/Process/FetchNext.......... I can say ....... select top 1 from #Temp/process/Delete (that row) from #Temp/Select Top1 from #Temp
which is technically set-based but probably not what you meant.


Give me a little time and I will show you. Unfortunately I have other things I need to do right now. I should have some time later tonight to throw something together.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
mwitthoft
mwitthoft
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 19
Aha! The answer is in today's xkcd comic.
Kurt W. Zimmerman
Kurt W. Zimmerman
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1064 Visits: 1396
Lynn Pettis (1/10/2014)
If you are routinely using cursors in your production code you are more than likely doing things wrong. Cursors should be avoided for most database activity. The only time I have used cursors has been for maintenance routines or one off processes, and even then I try to limit them as much as possible.



Well Lynn... One of the issues/problems I'm facing is a 3rd party software product that I will be ultimately supporting. What I've seen is the SQL code base is seriously out of date. The latest issue where I blew a cork over was a CURSOR in a TRIGGER.... w00tw00tw00tw00t

I have suggested to my boss that the 3rd party software provider get themselves a GOOD SQL Server data modeler and to start cleaning up the legacy code.

It is like looking at code through a time warp. ANSI 89 code standards.

My battle rages on....
Kurt

Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY

http://www.linkedin.com/in/kurtwzimmerman
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search