Change encoding (collation?) of SQL Server 2008 R2 to UTF-8

utf-8 sql-server-2008-r2 collation ucs2
Ahatius picture Ahatius · Nov 23, 2012 · Viewed 32.9k times · Source

We'd like to move our Confluence system to a SQL Server 2008 R2. Now, since Confluence uses UTF-8 encoding, I'd need a database using the same encoding (I guess that's the collation?).

There's the command 

alter database confluence set collation COLLATION_NAME

Now, as it seems, there is no utf-8, and as I found out SQL Server uses ucs-2 which is basically the same. But I can't figure out what the collation name of ucs-2 would be? Does somebody know about that?

Edit: I do see the difference between encoding and collation now. The Confluence documentation suggests that I should create an schema which relies on UCS-2 (because MS SQL has missing support for UTF-8). I have looked trough the Managment Studio and I found an entry for schemas in the Security directory of the database. However, I can not figure out how to assign UCS-2 encoding to the schema. What do I have to realize this in the Managment Studio to do so (or which query should I use)?

Answer

steoleary picture steoleary · Nov 7, 2013

According to the confluence documentation you should set the collation to SQL_Latin1_General_CP1_CS_AS

We have followed this document and have had a successful confluence deployment on SQL Server 2008 R2:

Database Setup for SQL Server

Related questions

How to write UTF-8 characters using bulk insert in SQL Server?

I am doing a BULK INSERT into sqlserver and it is not inserting UTF-8 characters into database properly. The data file contains these characters, but the database rows contain garbage characters after bulk insert execution. My first suspect was the …

Read More
Encode String to UTF-8

I have a String with a "ñ" character and I have some problems with it. I need to encode this String to UTF-8 encoding. I have tried it by this way, but it doesn't work: byte ptext[] = myString.getBytes(); String value = …

Read More
Working with UTF-8 encoding in Python source

Consider: $ cat bla.py u = unicode('d…') s = u.encode('utf-8') print s $ python bla.py File "bla.py", line 1 SyntaxError: Non-ASCII character '\xe2' in file bla.py on line 1, but no encoding declared; see http://…

Read More