Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»

Cursor fetch loops endlessly Expand / Collapse
Author
Message
Posted Friday, January 10, 2014 1:11 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, March 2, 2014 10:27 AM
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.
Post #1529959
Posted Friday, January 10, 2014 1:19 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:42 PM
Points: 23,009, Visits: 31,510
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.



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)
Post #1529961
Posted Friday, January 10, 2014 1:23 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:21 PM
Points: 13,083, Visits: 11,918
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)
Post #1529965
Posted Friday, January 10, 2014 1:36 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, March 2, 2014 10:27 AM
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

Post #1529969
Posted Friday, January 10, 2014 2:30 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:44 AM
Points: 7,019, Visits: 12,915
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
Post #1529977
Posted Sunday, January 12, 2014 4:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, March 2, 2014 10:27 AM
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.
Post #1530090
Posted Sunday, January 12, 2014 5:08 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:44 AM
Points: 7,019, Visits: 12,915
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
Post #1530091
Posted Sunday, January 12, 2014 6:09 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:42 PM
Points: 23,009, Visits: 31,510
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.



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)
Post #1530093
Posted Monday, January 13, 2014 5:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, March 2, 2014 10:27 AM
Points: 6, Visits: 19
Aha! The answer is in today's xkcd comic.
Post #1530251
Posted Monday, January 13, 2014 8:41 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 8:52 AM
Points: 980, Visits: 1,324
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....

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
Post #1530321
« Prev Topic | Next Topic »

Add to briefcase «««12345»»

Permissions Expand / Collapse