BUG: If RPC Is Used, SQL Server ODBC Driver Converts in Place

In a self-written ODBC program that executes a prepared statement at a SQL Server (set up with an OEM code page, such as code page 437 or 850) the data in the buffer passed in the rgbValue parameter of SQLBindParameter is converted from ANSI to OEM after issuing the SQLExecute. This can be observed when using extended characters in the character buffer.

In Microsoft Access, when you retrieve records in an attached ODBC table, each field in a record contains the "#Deleted" error message. This happens if the attached SQL Server table contains extended characters in the primary key of that record.

This problem occurs if all of the following conditions are true:

• The "Generate stored procedures for prepared statement" option is checked in the ODBC datasource used.
• The "Convert OEM to ANSI characters" option is checked in the ODBC datasource used.
• A prepared statement is used in ODBC.
• A parameter of SQLBindParameter contains an extended character.

The ODBC SQL Server driver invokes the stored procedure upon SQLExecute by remote procedure call (RPC). In this scenario, a passed parameter is converted in place. Because Microsoft Access reuses these parameters to match the returned data to the values of the primary key, it shows "#Deleted" in the affected records.

To work around this problem, do one of the following, as appropriate for your application:

• Do not select the "Generate stored procedures for prepared statement" option.
• Move your SQL database to a SQL Server running on the ANSI code page and do not select the "Convert OEM to ANSI characters" option.
• Use SQLExecDirect instead of SQLPrepare or SQLExecute, where needed.

Microsoft has confirmed this to be a problem in the products listed at the beginning of this article. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

This problem also occurs if you use the MS Code page Translator instead of the "Convert OEM to ANSI characters" check box.