BUG: Optimistic Concurrency Error with DYNAMIC Cursors

• Microsoft SQL Server, version 6.5
• Microsoft Open Database Connectivity, version 2.5

SYMPTOMS

When you attempt to call the open Database Connectivity (ODBC) function SQLSetPos() multiple times with the SQL_UPDATE option on a DYNAMIC cursor, you may incorrectly receive the following error message:

   szSqlState = "01S03", *pfNativeError = 16934,
   szErrorMsg = "[Microsoft][ODBC SQL Server Driver][SQL Server]
                Optimistic concurrency check failed, the row was
                modified outside of this cursor"

WORKAROUND

To work around this problem, switch to a keyset cursor on SQL Server 6.5 Service Pack 1 or later. To obtain SQL Server 6.5 Service Pack 1, see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q154549
   TITLE     : INF: How to Obtain SQL Server 6.5 Service Pack 1

STATUS

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

MORE INFORMATION

Error 16934 does not occur on very small tables (fewer than 100 rows). The error may occur on the second call to SQLSetPos, as in the following example:

   SQLSetStmtOption(hstmt, SQL_CONCURRENCY, SQL_CONCUR_VALUES);
   SQLSetStmtOption(hstmt, SQL_CURSOR_TYPE, SQL_CURSOR_DYNAMIC);
   SQLExecDirect(hstmt, "select command", SQL_NTS);
   SQLExtendedFetch(hstmt, SQL_FETCH_NEXT);
   SQLSetPos(hstmt, SQL_UPDATE, SQL_LOCK_NO_CHANGE);
   SQLExtendedFetch(hstmt, SQL_FETCH_NEXT);
   SQLSetPos(hstmt, SQL_UPDATE, SQL_LOCK_NO_CHANGE);