Article ID: 175127
Article Last Modified on 10/3/2003
APPLIES TO
- Microsoft SQL Server 6.5 Standard Edition
This article was previously published under Q175127
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:
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 kbbug kbusage kbsqlserv650bug KB175127