BUG: Character Translation Causes 105, Unclosed Quote Error

Last reviewed: December 1, 1997
Article ID: Q175127
The information in this article applies to:
  • Microsoft SQL Server, version 6.5
BUG #: 17353 (sqlbug_65)

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.


Additional query words: t-sql tran-sql trans-sql tsql transql transsql
Keywords : kbbug6.50 SSrvGen kberrmsg kbusage
Version : Windows:6.5
Platform : WINDOWS
Issue type : kbbug
Solution Type : kbworkaround


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: December 1, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.