SQL SERVER 2008 R2 can't support UTF-8 records.

[Centralweb]

Hi.
We installed PLESK 11.5 on a windows server 2008 R2 and every things are OK. But there is an issue about SQL SERVER 2008 R2 and it's that customers can not store their data on their databases in UTF-8 and their data store like this : ??????

What we should do? How we can enable UTF-8 support for Microsoft SQL Server 2008 R2?


Best regards

 

[IgorG]

Could you please provide more details? Step-by-step instruction how this issue can be reproduced would be very useful.
Thanks.

 

[Centralweb]

It's simple. We installed PLESK by PLESK auto installer on a windows server 2008 R2. Customers create SQL SERVER database and when they want to insert UTF-8 characters (Such as Arabic), it store "?" character instead of UTF-8 characters.

 

[IgorG]

How he performs this insertion?

 

[KhanhH]

The solution is: just add CODEPAGE='65001' inside the with statement of the bulk insert. (65001=codepage number for UTF-8). Might not work for all unicode characters as suggested by Michael O, but at least it works perfect for latin-extended, greek and cyrillic, probably many others too.

Note: MSDN documentation says utf-8 is not supported, don't believe it, for me this works perfect in SQL server 2008, didn't try other versions however.

e.g.:

BULK INSERT #myTempTable 
FROM  'D:\somefolder\myCSV.txt'+
WITH 
    ( 
        CODEPAGE = '65001',
        FIELDTERMINATOR = '|',
        ROWTERMINATOR ='\n'
    );

If all your special characters are in 160-255 (iso-8859-1 or windows-1252), you could also use:

BULK INSERT #myTempTable 
FROM  'D:\somefolder\myCSV.txt'+
WITH 
    ( 
        CODEPAGE = 'ACP',
        FIELDTERMINATOR = '|',
        ROWTERMINATOR ='\n'
    );