Article ID: 171910
Article Last Modified on 2/12/2007
APPLIES TO
- Microsoft SQL Server 6.0 Standard Edition
- Microsoft SQL Server 6.5 Standard Edition
- Microsoft ODBC Driver for Microsoft SQL Server 2.5
- Microsoft ODBC Driver for Microsoft SQL Server 2.65
- Microsoft Access 2.0 Standard Edition
- Microsoft Access 95 Standard Edition
- Microsoft Access 97 Standard Edition
This article was previously published under Q171910
BUG #: 17119 (SQL 6.5)
SYMPTOMS
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.
CAUSE
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.
WORKAROUND
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.
STATUS
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.
MORE INFORMATION
This problem also occurs if you use the MS Code page Translator instead of the "Convert OEM to ANSI characters" check box.
Additional query words: Ansi2Oem Oem2Ansi AnsiToOem OemToAnsi CharToOem OemToChar cp codepage
Keywords: kbbug kbinterop kbprogramming kbusage KB171910
