Microsoft KB Archive/164678

{|
 * width="100%"|

PRB: Incorrect Syntax Near 'FOR' When Calling Stored Procedure

 * }

Q164678

-

The information in this article applies to:


 * The Microsoft Foundation Classes (MFC), included with:
 * Microsoft Visual C++, version 1.52

-

SYMPTOMS
Opening a snapshot-type recordset and calling a SQL Server stored procedure in Open throws a CDBException with the following error message:

     DBMS: Microsoft SQL Server, Version: 06.00.0121 Incorrect syntax near the keyword 'FOR'. State:37000[Microsoft][ODBC SQL Server Driver][SQL Server]

CAUSE
When you open a snapshot with the default settings, the 16-bit MFC ODBC classes add a "FOR UPDATE OF" clause at the end of the SQL statement. The SQL Server ODBC drivers (2.10.2401 or later) do not accept this clause with the CALL statement.

RESOLUTION
Open the recordset as readOnly when you use a CALL statement to execute a stored procedure.

    MyCRecordset rs; rs.Open(CRecordset::snapshot,"{CALL sp_myproc}";    CRecordset::readOnly);

MORE INFORMATION
This behavior is by design.

The Microsoft SQL Server ODBC driver version 2.00.1912 that ships with Visual C++ 1.52, accepts the [ASCII 145]FOR UPDATE OF[ASCII 146] clause. However, newer SQL Server ODBC drivers (version 2.10.2401 or later) will return an error in SQLPrepare when you call a stored procedure in a snapshot-type recordset with default options.

The exception is thrown when SQLPrepare is executed at line 1226 in dbcore.cpp.

The 32-bit MFC ODBC classes do not add the "FOR UPDATE OF" clause to a CALL statement.