Sql Server 2005 - Changing Collation

Question

Hi, suddenly we have to save kanji (Japanese) in a couple of columns of two or three tables. I have tried to save to the db using the current collation which is SQL_Latin1_General_CP1_CI_AS. However, I just get '?' for each character. I am going to need to change the collation to Japanese_CI_AS I presume. My question is can I change the collation of the whole database? If not if I change the collation of a column will that override the database settings thus allowing me to store the kanji in that column correctly?

Any pointers much appreciated.

Answer 1

If you only need to store Kanji in a handful of columns then you should set those columns to use the Japanese collation. Setting the database collation to Kanji would be overkill - and would not change the collation of the columns IIRC - the collation specified at database level is the default collation which will affect creates from the point it is set onwards rather than affecting existing tables etc.

Answer 2

I second Will A. And I don't think it is possible to change collation of entire database or installed SQL Server instance.

You can use alter statements to change the collation.

This article may help

Setting and Changing the Column Collation

Answer 3

You can

1. rebuild master to change server collations (master, msdb, tempdb, model)
   • change the user db collation so all text columns have a new collation (requires scripting, import/export etc)
   • add collation settings to thje few columns you need

I suggest the 3rd option...