BUG: Character Translation Causes 105, Unclosed Quote Error (175127)

SYMPTOMS

When you insert a character through an ODBC data source with either OEM-to- ANSI translation enabled or a code page translation set, some characters are translated to an apostrophe (ANSI 39). When the Generate Stored Procedures option is disabled and a parameter is passed containing these translated characters, SQL Server returns an error 105:

Unclosed quote before the character string ')'.

NOTE: The apostrophe is also referred to as a single quotation mark or single quote character.

CAUSE

If a single quotation mark is passed in the parameter, this is replaced by two single quotation marks and the insert executes correctly.

All of the following conditions must be met:

The Generate Stored Procedure for Prepared Statement option must be disabled for the data source.

-and-
OEM-to-ANSI conversion must be enabled or a code page translator selected for the data source.

-and-
The client application prepares the statement with parameter markers.

-and-
One of the parameters contains a string which includes an extended character which in the translation enabled for the data source is converted to the single quotation mark (ANSI 39).

When a Transact-SQL statement is sent to the server, the string type parameters are delimited by single quotation marks, and the translated single quotation mark character in the parameter makes the statement ambiguous.

WORKAROUND

Use any of the following methods to work around the problem:

Enable Generate Stored Procedure for Prepared Statement. The stored procedure is created and the parameters to the stored procedure are passed delimited by double quotation marks so that the problem does not occur.

-or-
Disable translation if you don't need this feature. The character is not translated and will be stored in the server code page as the relevant character.

-or-
In the client application, parse the parameters before binding them to the prepared statement and make appropriate substitutions for characters which are causing the problem. Substituting the single quotation mark before binding the parameter works as this will be replaced by two single quotation marks before being passed to SQL Server.

STATUS

Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.