Registrations are now open. Join us today!
There is still a lot of work to do on the wiki yet! More information about editing can be found here.
Already have an account?

Microsoft KB Archive/171910

From BetaArchive Wiki
Knowledge Base


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