BUG: Cursor Fetch Within Stored Procedure Behavior Inconsistent

 Last reviewed: April 9, 1997
Article ID: Q155220
The information in this article applies to:
  • Microsoft SQL Server, version 6.5

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 : kbbug6.50 kbprg SSrvProg SSrvStProc
Version : 6.5
Platform : WINDOWS

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: April 9, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.