SQLFetch() fails with the SQLState 24000 (Invalid Cursor)

Question

First of all this question is not a duplicate of:

as it occurs with a clean single SQL Environment that has not executed anything before!

I am trying to execute a Stored Procedure with a prepared SQL Statement with the following C++ ODBC Code:

void ItemDBManager::UpgradeItem(SQLHSTMT hstmt, QUERY_UPGRADE_ITEM_PARAMS* upgradeItemParams)
{

// Preparing of the Query
RETCODE ret = SQLPrepare(hstmt, (UCHAR*)"{call dbo.upgrade_Item(?,?)}", SQL_NTS);
if (ret != SQL_SUCCESS && ret != SQL_SUCCESS_WITH_INFO)
{
   // Get diagnostics for the error and log it to a file
   ProcessSQLError(SQL_HANDLE_STMT, hstmt, "dbo.upgrade_Item failed!");
   SQLFreeStmt(hstmt, SQL_CLOSE);
   return;
}

// Binding of Parameters
SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_SBIGINT, SQL_BIGINT, 0, 0, &upgradeItemParams->OldItemUniqueNumber, 0, NULL);
SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_ULONG, SQL_INTEGER, 0, 0, &upgradeItemParams->UpgradedItemUID, 0, NULL);

// Binding of the Result
SQLINTEGER cb[44];
fill_n(cb, 44, SQL_NTS);
ITEM newItem; // My struct to hold the data returned by the Stored Procedure

// Binding of result Columns
int coldIdx = 0;

SQLBindCol(hstmt, ++colIdx, SQL_C_SBIGINT, &storeItem.UniqueNumber, 0, &cb[colIdx]);
SQLBindCol(hstmt, ++colIdx, SQL_C_ULONG, &storeItem.AccountUniqueNumber, 0, &cb[colIdx]);
[...]

// Zeroing the resulting struct
memset(&newItem, 0x00, sizeof(ITEM));

// Execution of the Statement
ret = SQLExecute(hstmt);
if (ret != SQL_SUCCESS && ret != SQL_SUCCESS_WITH_INFO)
{
   // Get diagnostics for the error and log it to a file
   ProcessSQLError(SQL_HANDLE_STMT, hstmt, "dbo.upgrade_Item failed!");
   SQLFreeStmt(hstmt, SQL_CLOSE);
   return;
}

// Fetching of a single result row
ret = SQLFetch(hstmt);
if (ret == SQL_NO_DATA || (ret != SQL_SUCCESS && ret != SQL_SUCCESS_WITH_INFO))
{
   // <-- The Debugger enters here with the return code of -1 !!!!

   // Get diagnostics for the error and log it to a file
   ProcessSQLError(SQL_HANDLE_STMT, hstmt, "dbo.upgrade_Item failed!");
   SQLFreeStmt(hstmt, SQL_CLOSE);
   return;
}

[...] // Further Handling of the resulted ITEM Struct and freeing of the SQL Statement

}

The debugger enters the last IF-Statement with the ReturnCode of -1 which is a general SQL_ERROR.

By getting the diagnostics Rect of the error, I get the following output:

06-24 14:05:19| szSqlState    = 24000
06-24 14:05:19| pfNativeError = 0
06-24 14:05:19| szErrorMsg    = [Microsoft][ODBC SQL Server Driver]Invalid Cursorstatus 
06-24 14:05:19| pcbErrorMsg   = 58

06-24 14:05:19| ODBCRowNumber = -1 
06-24 14:05:19| SSrvrLine     = -1 
06-24 14:05:19| SSrvrMsgState = 0 
06-24 14:05:19| SSrvrSeverity = 0 
06-24 14:05:19| SSrvrProcname =  
06-24 14:05:19| SSrvrSrvname  =

Executing the Query directly in Microsofts SQL Management Studio gives me a perfect result and not any errors.

What is the reason of this error? How can I debug it further?

Additionally the content of the Stored Procedure dbo.upgrade_Item:

-- Stored Procedure dbo.upgrade_Item
@i_OldStoreUID BIGINT,
@i_NewItemNum  INT

AS

DECLARE @insertedIDs TABLE(Id BIGINT)

-- Updating of the ItemStore Table
INSERT INTO td_ItemStore (*my columns*)
OUTPUT INSERTED.UniqueNumber INTO @insertedIDs 
SELECT *my columns*, @i_NewItemNum
FROM td_ItemStore
WHERE UniqueNumber = @i_OldStoreUID

-- Returning the new inserted Item
SELECT * FROM td_ItemStore WHERE UniqueNumber = (SELECT TOP 1 Id FROM @insertedStoreUIDs)

Any help is highly appreciated!

`Invalid Cursorstatus` Primarily happens if the results from the query are in error. Try breaking your stored procedure into 2(do insert in one and return results in other) and check. — DumbCoder, Jun 24 '15 at 12:58
@DumbCoder ok thank you, this is kind of bad in my situation though as I need the newly created Identity to be able to find this new inserted Item again. Is there another way around this? — Vinz, Jun 24 '15 at 13:04
No break into just to confirm if both steps are working fine. Then fix the one not working and use the combined one. You need to figure out first which part of the stored procedure is breaking. — DumbCoder, Jun 24 '15 at 13:21

score 4 · Accepted Answer · edited May 23 '17 at 11:50

Apparently I had the same issue as in this question: PDO with MSSQL returns Invalid Cursor

The problem was in my Stored Procedure. In order to get a correct cursor I had to disable the output of effected rows by setting NOCOUNT ON

The fixed procedure:

-- Stored Procedure dbo.upgrade_Item
@i_OldStoreUID BIGINT,
@i_NewItemNum  INT

AS

BEGIN
   SET NOCOUNT ON;

   DECLARE @insertedIDs TABLE(Id BIGINT)

   -- Updating of the ItemStore Table
   INSERT INTO td_ItemStore (*my columns*)
   OUTPUT INSERTED.UniqueNumber INTO @insertedIDs 
   SELECT *my columns*, @i_NewItemNum
   FROM td_ItemStore
   WHERE UniqueNumber = @i_OldStoreUID

   -- Returning the new inserted Item
   SELECT * FROM td_ItemStore WHERE UniqueNumber = (SELECT TOP 1 Id FROM @insertedStoreUIDs)
END

An alternative to enabling nocount is to read about SQLMoreResults as you need to call it to step through the possible multiple results your procedure generates. — bohica, Jun 29 '15 at 09:41

SQLFetch() fails with the SQLState 24000 (Invalid Cursor)

1 Answers1