Multilingual characters issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Multilingual characters issue

Author

Topic

abhwhiz
Starting Member

37 Posts

Posted - 2007-09-17 : 07:41:58

I have table with an varchar and ntext columns into which i am trying insert some multilingual values like russian and polish. The issue is when i copy the data from unicode text file, which was created from an excel sheet, sql puts " at the beginning and end of certain values. If we update the table with to remove the quotes, sql doesnt preserve the russian/polish values , it removes special characters. It looks like sql is deliberately putting these quotes to preserve the multilingual values. Is there any fix for this? Thanks in advance.

I am using bul insert to insert values from text files. i am using sql 2005 too, with SP2.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2007-09-17 : 07:43:23

have you tried changing the varchar to nvarchar?

abhwhiz
Starting Member

37 Posts

Posted - 2007-09-17 : 08:07:20

Let me explain the issue a bit.

This is the value i am trying to insert.
Twoja rezerwacja jest w trakcie realizacji, nasz

konsultant skontaktuje siê z Tob¹, aby j¹ potwierdziæ'

sql stores the value with quotes, like this
"Twoja rezerwacja jest w trakcie realizacji, nasz

konsultant skontaktuje siê z Tob¹, aby j¹ potwierdziæ'"

not like this

Twoja rezerwacja jest w trakcie realizacji, nasz

konsultant skontaktuje siê z Tob¹, aby j¹ potwierdziæ'

if i remove "" using an update statement the value looks like

Twoja rezerwacja jest w trakcie realizacji, nasz konsultant skontaktuje sie z Toba, aby ja potwierdzic

strangely without the special characters...!!!

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2007-09-17 : 08:08:59

Have you tried preceding your strings with N'?
http://support.microsoft.com/kb/239530

Have you made the field nvarchar instead?

abhwhiz
Starting Member

37 Posts

Posted - 2007-09-17 : 08:09:39

I am using BULK Insert

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2007-09-17 : 08:11:27

Command Option Description
bcp
-N
Causes the bcp utility to use the Unicode native format, which uses nativeCommand Option Description
Are you using the -N option for unicode?

bcp
-N
Causes the bcp utility to use the Unicode native format, which uses native (database) data types for all noncharacter data and Unicode character data format for all character (char, nchar, varchar, nvarchar, text, and ntext) data.

BULK INSERT
DATAFILETYPE = 'widenative'
Use Unicode native format when bulk importing data.

(database) data types for all noncharacter data and Unicode character data format for all character (char, nchar, varchar, nvarchar, text, and ntext) data.

BULK INSERT
DATAFILETYPE = 'widenative'
Use Unicode native format when bulk importing data.

abhwhiz
Starting Member

37 Posts

Posted - 2007-09-17 : 08:37:53

I used
BULK INSERT #temp FROM '\\<dbserver>\<path>p\ru.txt'
WITH (
DATAFILETYPE = 'widenative',
FIELDTERMINATOR = '\t',
ROWTERMINATOR = '\n'
)

and i got the error

Msg 4866, Level 16, State 7, Line 1
The bulk load failed. The column is too long in the data file for row 1, column 1. Verify that the field terminator and row terminator are specified correctly.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

and this is the command i am using to create the table

CREATE TABLE #TEMP
(
KEYNAME VARCHAR(256),
LOCALE VARCHAR (20),
VALUE NTEXT
)

any ideas?

abhwhiz
Starting Member

37 Posts

Posted - 2007-09-17 : 08:44:45

Now i found out that the issue is when i converts the excel sheet to unicode text file it automatically puts " for some values. I know this is not the right place to ask Excel doubts, but would be great if you can tell me why...

abhwhiz
Starting Member

37 Posts

Posted - 2007-09-18 : 00:40:30

The solution is to copy paste the data directly into the text file which is in unicode format

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources