Insert Extended Character using OSQL Utility


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’

Advertisements

Thanks for the comment, will get back to you soon... Jugal Shah

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s