BUG: Cursor Fetch Within Stored Procedure Behavior Inconsistent

ID: Q155220

The information in this article applies to:

SYMPTOMS

When cursor is declared and fetched within stored procedure, "Fetch Cursor" displays inconsistent behavior between DYNAMIC and SCROLLABLE cursors. "FETCH NEXT FROM cursor INTO @variables" with DYNAMIC cursor results in the "0 row(s) affected" message, while the SCROLLABLE cursor does not return any message.

Because the default behavior for cursor has changed from "KEYSET DRIVEN (or SCROLLABLE)" in SQL Server 6.0 to "DYNAMIC" in SQL Server 6.5, this appears to users as stored procedure behavior changes from SQL Server 6.0 to SQL Server 6.5.

NOTE: In DYNAMIC cursor, DONE_INPROC bit is not set when "FETCH" is executed, therefore it always returns the 0 row count.


WORKAROUND

Create the cursor as "SCROLL CURSOR" or use "SET NOCOUNT ON" within the stored procedure, so that you do not receive the "0 row(s) affected" message.


STATUS

Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.


MORE INFORMATION

This problem may affect ODBC applications migrated from SQL Server 6.0 to 6.5. Each fetch against these dynamic cursors in stored procedures under SQL Server 6.5 now returns a result set to an ODBC application consisting of the message: 


   0 row(s) affected

ODBC applications not coded to handle these new result sets can experience different errors. The most common error is that an ODBC application's logic will not be expecting these result sets from a procedure, and will not call SQLMoreResults() until it gets a SQL_NO_DATA_FOUND return code. In this case, the ODBC application will attempt a new command while there are "0 rows(s) affected" result sets still pending, and will receive the following error:
szSqlState = "24000", pfNativeError = 0,
szErrorMsg="[Microsoft][ODBC SQL Server Driver]
Invalid cursor state"

The above error may also occur if the ODBC application logic assumes it is on a particular result set it expects from the procedure, but is in fact on one of the result sets containing only the message "0 row(s) affected". In this case SQLBindCols may return SQL_SUCCESS, but the first fetch attempt will receive the "Invalid cursor state" error. Other errors may occur depending on the logic in the ODBC application.

Additional query words: cursor 


Keywords          : kbprg SSrvProg SSrvStProc kbbug6.50 
Version           : 6.5
Platform          : WINDOWS 
Issue type        :

Last Reviewed: April 1, 1999