Insert Extended Characters into a SQL Server Table Using OSQL and TSQL

By: Jugal Shah | Read Comments (1) | Print Jugal has 8+ years of extensive SQL Server experience and has worked on SQL Server 2000, 2005, 2008 and 2008 R2. Related Tips: More

Problem
The OSQL utility uses ODBC to communicate with SQL Server. The 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.

Solution
We can resolve this issue by configuring DNS or T-SQL . To resolve this issue we will take the below example.

Steps to reproduce the issue

create table Example
(
 id int identity(1,1),
 extendedCharactervalue varchar(1000)
);
insert into Example(extendedCharactervalue) values('Rrotégé Company, LLC')

Step 2 Save below INSERT query into text file/SQL file. I have named the file Query.txt

insert into Example(extendedCharactervalue) values('Rrotégé Company, LLC')

Step 3 Execute the below OSQL statement from command prompt

Solution - I Insert Extended character using DNS with OSQL

Step 1.6 Select the database name from list and click on Next button

Solution - II Using T-SQL

-- ascii function retuns the ascii value of extended character
select ascii('é')
-- char function converts the ascii value to character
select char(233)
insert into Example(extendedCharactervalue) 
select 'Rrot' + char(233) + 'g' + char(233) + ' Company, LLC'

Next Steps

• Check out the above solution by adding more extended characters
• Check the solution one without using a DSN and only saving script in the unicode file format.
• Search out more OSQL switches.

Share:

Share

Tweet

Connect with MSSQLTips.com Follow us on twitter... Let's connect on facebook! Join us on Google+

Comments and Feedback:

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code