BUG: Character Translation Causes 105, Unclosed Quote Error
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 : kberrmsg kbusage SSrvGen kbbug6.50
Version : Windows:6.5
Platform : WINDOWS
Issue type : kbbug