January 10, 2014 at 1:11 pm
<< 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.
January 10, 2014 at 1:19 pm
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.
January 10, 2014 at 1:23 pm
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 10, 2014 at 1:36 pm
<<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
January 10, 2014 at 2:30 pm
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!
January 12, 2014 at 4:48 am
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.
January 12, 2014 at 5:08 am
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.
January 12, 2014 at 6:09 am
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.
January 13, 2014 at 5:28 am
Aha! The answer is in today's xkcd comic.
January 13, 2014 at 8:41 am
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.... :w00t::w00t::w00t::w00t:
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
January 14, 2014 at 4:55 am
Customer: "My car recently started to make a weird noise when I run the air conditioning."
Auto mechanic: "You really shouldn't run the air conditioning. Good drivers use the air conditioning only rarely."
Viewing 11 posts - 31 through 40 (of 40 total)
You must be logged in to reply to this topic. Login to reply