Problem: OSQL utility uses ODBC to communicate with the server. User’s problem is that the ODBC driver he is using to connect to the database is performing translations on the character data in the T-SQL script. Extended characters, which are not in the standard ASCII character set, are translated by the driver based on drive settings. The character translation option is ON by default when SQL Server executes scripts through the OSQL utility.
Below query is inserting garbage data in the table.
CREATE TABLE #temp(col1 varchar(40) NOT NULL )
INSERT INTO #temp VALUES( ‘Tëst’ )
SELECT col1 FROM #temp
DROP TABLE #temp
Solution: By using Unicode script files and converting the column to Unicode, user can avoid the character translation. For that user needs to add N against the column, which is already added.
Save As below script file as UNICODE file
CREATE TABLE #temp(col1 varchar(40) NOT NULL )
INSERT INTO #temp VALUES( ‘Tëst’ )
SELECT col1 FROM #temp
DROP TABLE #temp
User needs to do the following with the ODBC DSN to execute the scripts successfully, without any translation:
1. Create an ODBC system data source called MyDSN on the machine where he is executing OSQL with the “Perform translation for character data” option cleared
2. Specify this data source name as a parameter to OSQL so that OSQL can read the DSN settings and use them upon connection to SQL Server.
osql -S. -itest.sql –DMyDSN
OR
User needs to develop a script which can pass the ASCII value
Select ASCII(‘ë’)
INSERT INTO #temp (Col1) Select ‘T’ + chr(233) + ‘st’
