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.
