1

I typically avoid cursor operations like the plaque, however, I have come across my first problem where using a cursor outperforms a query. So I am forced to use it.

I have created an elaborate reporting stored procedure that is used by many users. I use a cursor to iterate a list and insert data into a @TEMP_TABLE, finally selecting the temp table as result set.

The cursor is used as follows:

DECLARE HIGHLIGHTS_REPORT_CURSOR CURSOR LOCAL FOR
SELECT RowID, UserID,GradeID,ClassID,MenuSetID,CurrentSequence FROM @DATA   
OPEN HIGHLIGHTS_REPORT_CURSOR
FETCH NEXT FROM HIGHLIGHTS_REPORT_CURSOR INTO @RowID,@UserID,@GradeID,@ClassID,@MenuSetID,@CurrentSequence
WHILE(@@FETCH_STATUS=0)BEGIN
    ...
    FETCH NEXT FROM HIGHLIGHTS_REPORT_CURSOR INTO @RowID, @UserID,@GradeID,@ClassID,@MenuSetID,@CurrentSequence
END
CLOSE HIGHLIGHTS_REPORT_CURSOR
DEALLOCATE HIGHLIGHTS_REPORT_CURSOR

So all is well, the report moved through QA and no problems until user based ramped up now I am getting exceptions logs sent to me sporadically with the following error:

Server Log Reference ID : b91a8f4a-b944-4355-bba3-2855fd126c2b
Message : A cursor with the name 'HIGHLIGHTS_REPORT_CURSOR' does not exist.
Source : HighlightsReport

Stack Trace :    at System.Data.SqlClient.SqlConnection.OnError(SqlExceptionexception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)

I can state with 100% confidence there is not another cursor with the same name. There are no errant CLOSE\DEALLOCATE CURSOR calls between BEGIN and END.

I learned that SQL Server defaults the "default cursor" database property to GLOBAL during installation. Since the report procedures are the only SP's where cursors are used I have changed the "default cursor" to LOCAL.

That did not help:( Maybe "default cursor" is a setting the requires a restart. Instead of restarting I used the LOCAL attribute of the CURSOR declaration.

This ensures that my cursor is scoped to the SP but still getting the error above???

I am starting to think this is a concurrency issue with multiple connections hitting the SP at the same time. This would explain the sporadic behavior and why this was not caught during QA.

Would it be possible that two connections call the procedure at approximately the same time in which one connection hits the DEALLOCATE HIGHLIGHTS_REPORT_CURSOR just before the second connection hits the FETCH NEXT FROM HIGHLIGHTS_REPORT_CURSOR INTO block??

I assumed using LOCAL ensured each connection would have its copy but it seems to not be the case? Any ideas?

Updated. This is bizarre I got three exception messages stacked into one. I wonder if a client connection calling the same sp in quick succession cause something similar.

Server Log Reference ID : b91a8f4a-b944-4355-bba3-2855fd126c2b
Message : A cursor with the name 'HIGHLIGHTS_REPORT_CURSOR' does not exist.
          A cursor with the name 'HIGHLIGHTS_REPORT_CURSOR' does not exist.
          A cursor with the name 'HIGHLIGHTS_REPORT_CURSOR' does not exist.
Source : HighlightsReport

Update 2 The part that makes me think this is a user concurrency issue is the fact that these errors always come in groups of two. I receive an exception report with the error noted above from two different users at the same time.

6
  • Maybe there is some type of connection interruption happening; this certainly should not have anything to do with two users running the code at the same time. You can try making it even more local by using a variable name for the cursor. But that's not even what LOCAL / GLOBAL means - it's about being able to reference the cursor from an outer (calling) scope. Imagine if two users could see into each other's cursors inadvertently? What an absolute mess that would be. Did you search your codebase for potentially other procedures etc. that might reference the same cursor name? Aug 19, 2014 at 17:59
  • A great idea is to prefix your cursor name with a context of sorts, like the name of your stored procedure. Though it looks like you did something like that already? Could you (inadvertedly) be calling this stored procedure recursively? Aug 19, 2014 at 20:51
  • I have debugged this to no end. Aaron Bertrand, Thanks I was certain that there is a clear partition between connections and cursor instances, I just needed a sanity check. Daniel Hutmacher, I never leave branch logic between a cursor creation and deallocation and I have performed a plain text scan for the SP in other SP's. I will look into the application and see if it is doing something weird that I can not seem to find. I have updated my answer with more findings. Thanks for the answer
    – Ross Bush
    Aug 19, 2014 at 21:47
  • Could it be the table variable @DATA that is causing the issue rather than the cursor. Have you tried replacing this with a temporary table?
    – MickyT
    Aug 19, 2014 at 23:19
  • No MickyT, this happens about 6-10 times a day during times of high demand. I can only try one thing a day almost. I have checked that the cursor exists prior to closing and de-allocating as a test today and still getting errors. The @DATA table variable is pretty plain. If you think that it will make a difference I can make that the test for tomorrow. I always thought it was better to use a table variable when the support was added in 2005.
    – Ross Bush
    Aug 19, 2014 at 23:25

1 Answer 1

1

So this was an oddity and I have yet to get down to the nuts and bolts. In a nutshell I enabled MARS on sql server connections a while back to fix an issue I was having with IoC container connections.

This fixed an exception that was similar to "You must close the current command yaddayaddayadda". However, Multiple Active Result Sets seems to lead to a concurrency issue in my case. I actually fixed the original issue that should have been fixed instead of enabling MARS. The fix was to implicitly create a new Data Context per call.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.