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

• The Microsoft Foundation Classes (MFC) included with: - Microsoft Visual C++ for Windows, 16-bit edition, 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 ‘FOR UPDATE OF’ 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.

REFERENCES

For more information, please see the following article in the Microsoft Knowledge Base: