Database Collation: Chinese, Japanese, Korean

My application supports multiple languages/locales in a single database. Some of our new customers want to support Chinese, Japanese, Korean, Italian, Spanish, and German in addition to English. Supporting the Latin based languages is not a problem. But I am having trouble finding a collation sequence that allows me to store the other double byte languages in the same database correctly.

I have found changing the data types from text, char, varchar to ntext, nchar, nvarchar and adding an N in front of the various strings that getting inserted seems to work:

insert into CONTENTDATA (recordid, xml)
values (newid(), N'<CHANNEL1><FILE1/><TEXT1><![CDATA[和红魔拉拉队的动感精神
]]></TEXT1><TEXT3><![CDATA[和红魔拉拉队的动感精神]]></TEXT3></CHANNEL1>');

But this is not going to be a practical solution for us. Is there a collation sequence that would allow us to store multiple locales like we do in Oracle (AL32UTF8)?

Thanks in advance

Dov RosenbergYou can store multiple languages in SQL Server (since version 7.0) using the Unicode data types as you have noted. In Setup, you can also choose the default Server collation.If you need a finer level of collation control subsequently, you can use the multiple collations per column feature in SQL Server 2000 / 2005. More details at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da_6ttf.asp, http://msdn.microsoft.com/SQL/2000/learn/internat/default.aspx.

Database Collation setting

I have a SQL Server 2005 database which is set for

SQL_Latin1_General_Cp1_CI_AS collation (Actually when the instance was created it was set to this collation).....I have lot og objects tables,views, fks, col constraints , triggers, default etc defined....

I need to change the collation of the database and subsequently affecting the collation of every single column to SQL_Latin1_General_Cp1_CS_AS

Now when I run the following command i gives me lot of errors saying that

ALTER DATABASE <dbName> COLLATE SQL_Latin1_General_Cp1_CS_AS

The column constranint <xx> is dependent on collation .....

The funny part is that this col constraint is defined on a float column instead of varchar column.....why is it giving error when it does not have any collation associated with it?

What are the steps that I need to take before running the alter collation on the database? (mean disabling fks constraints, col constraints etc,,,)

Any pointers will be greatly appreciated.

Regards

Imtiaz

It's not just straigt forward to change collation. My best experience is to (generate) script the old database and change the collation when creating a new one. After this you can import the tables into the new database with correct collation. If you use tempdb and don't want to use the collate clause - you need to rebuild the master database and all the system databases.

Hope this helps.

Database collation

how to change database collation?On Mon, 27 Feb 2006 20:01:44 +0100, Tale wrote:

>how to change database collation?
>
Hi Tale,
ALTER DATABASE databasename
COLLATE collationname
Hugo Kornelis, SQL Server MVP|||... and if the existing object's collation also need to be changed, so ALTE
R TABLE ... ALTER COLUMN
for each string column in each table.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:drpe02pkevmct79b5nl8tfct2mm3c12io4@.
4ax.com...
> On Mon, 27 Feb 2006 20:01:44 +0100, Tale wrote:
>
> Hi Tale,
> ALTER DATABASE databasename
> COLLATE collationname
> --
> Hugo Kornelis, SQL Server MVP|||Thanks
to you have any script for this operations - change collations for every
table / column
all in one
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%238MFa0vPGHA.2108@.TK2MSFTNGP10.phx.gbl...
> ... and if the existing object's collation also need to be changed, so
> ALTER TABLE ... ALTER COLUMN for each string column in each table.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
> news:drpe02pkevmct79b5nl8tfct2mm3c12io4@.
4ax.com...
>|||I haven't seen such script, but I haven't been looking for one either. Perha
ps you can find if you
Google?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Tale" <tales@.eunet.yu> wrote in message news:duueuf$gst$1@.news.eunet.yu...
> Thanks
> to you have any script for this operations - change collations for every
> table / column
> all in one
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:%238MFa0vPGHA.2108@.TK2MSFTNGP10.phx.gbl...
>

Database collation

how to change database collation?On Mon, 27 Feb 2006 20:01:44 +0100, Tale wrote:
>how to change database collation?
>
Hi Tale,
ALTER DATABASE databasename
COLLATE collationname
--
Hugo Kornelis, SQL Server MVP|||... and if the existing object's collation also need to be changed, so ALTER TABLE ... ALTER COLUMN
for each string column in each table.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:drpe02pkevmct79b5nl8tfct2mm3c12io4@.4ax.com...
> On Mon, 27 Feb 2006 20:01:44 +0100, Tale wrote:
>>how to change database collation?
> Hi Tale,
> ALTER DATABASE databasename
> COLLATE collationname
> --
> Hugo Kornelis, SQL Server MVP|||Thanks
to you have any script for this operations - change collations for every
table / column
all in one
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%238MFa0vPGHA.2108@.TK2MSFTNGP10.phx.gbl...
> ... and if the existing object's collation also need to be changed, so
> ALTER TABLE ... ALTER COLUMN for each string column in each table.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
> news:drpe02pkevmct79b5nl8tfct2mm3c12io4@.4ax.com...
>> On Mon, 27 Feb 2006 20:01:44 +0100, Tale wrote:
>>how to change database collation?
>>
>> Hi Tale,
>> ALTER DATABASE databasename
>> COLLATE collationname
>> --
>> Hugo Kornelis, SQL Server MVP
>|||I haven't seen such script, but I haven't been looking for one either. Perhaps you can find if you
Google?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Tale" <tales@.eunet.yu> wrote in message news:duueuf$gst$1@.news.eunet.yu...
> Thanks
> to you have any script for this operations - change collations for every
> table / column
> all in one
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:%238MFa0vPGHA.2108@.TK2MSFTNGP10.phx.gbl...
>> ... and if the existing object's collation also need to be changed, so ALTER TABLE ... ALTER
>> COLUMN for each string column in each table.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
>> news:drpe02pkevmct79b5nl8tfct2mm3c12io4@.4ax.com...
>> On Mon, 27 Feb 2006 20:01:44 +0100, Tale wrote:
>>how to change database collation?
>>
>> Hi Tale,
>> ALTER DATABASE databasename
>> COLLATE collationname
>> --
>> Hugo Kornelis, SQL Server MVP
>

database collation

how do you check database collation in sql 7.0Moe,
sp_helpsort
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"moe" <shlomoc@.ou.org> wrote in message
news:054501c37253$8aeff6e0$a101280a@.phx.gbl...
> how do you check database collation in sql 7.0sql

Database collation

Hello,
I have database migrated from SQL7 to SQL2K.
This database has collation 'comaptibility something bla bla'.
Other database4s located at this server has different collation name,
because were created under SQL2K. PRoblem is when I try to join tabels
between old database and new.
Is it possible to change this collation to another (from SQL2K) ?
--
Best Regards,
IksHi
It looks like you have collation compatibility problems.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da_8uer.asp
To change collations check out:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da_819v.asp
http://tinyurl.com/ky3q
"Iks" <jko74__usun__@.wp.pl> wrote in message
news:bi7igc$mol$1@.atlantis.news.tpi.pl...
> Hello,
> I have database migrated from SQL7 to SQL2K.
> This database has collation 'comaptibility something bla bla'.
> Other database4s located at this server has different collation name,
> because were created under SQL2K. PRoblem is when I try to join tabels
> between old database and new.
> Is it possible to change this collation to another (from SQL2K) ?
> --
> Best Regards,
> Iks
>|||Hi,
I'm not sure about that, but I think that the collations
you are trying to join are not compatible - why don't you
try to identify a collation that is equivalent from this
using in the database migrated from sql 7 and change the
collation for that specific column, i've never seen an
option that changes all collations from database at time !
Only in the installation you specify a collation that is
going to be used for the new created databases.
Sorry if Couldn't help you !
>--Original Message--
>Uzytkownik "Roberto Carrasco" <roberto_carrasco@.msn.com>
napisal w
>wiadomosci news:090701c36981$b03d9770$a301280a@.phx.gbl...
>> Hi,
>> If I understand your question --Yes SQLserver2000 Allows
>> you to have differente collation for database even to
>> columns !
>Yes, I know. But at this time I have on my server one
database
>(migrated from SQL7) with old-style collation. Other
databases
>were created with new default server collation.
>There is no problem until I have to compare two columns
from
>different databases (different collations) - join tables
from different
>databases.
>That's the reason I would like to unify collation for all
databases
>located on this server.
>--
>regards,
>Iks
>
>.
>

Database collation

how to change database collation?
On Mon, 27 Feb 2006 20:01:44 +0100, Tale wrote:

>how to change database collation?
>
Hi Tale,
ALTER DATABASE databasename
COLLATE collationname
Hugo Kornelis, SQL Server MVP
|||... and if the existing object's collation also need to be changed, so ALTER TABLE ... ALTER COLUMN
for each string column in each table.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:drpe02pkevmct79b5nl8tfct2mm3c12io4@.4ax.com...
> On Mon, 27 Feb 2006 20:01:44 +0100, Tale wrote:
>
> Hi Tale,
> ALTER DATABASE databasename
> COLLATE collationname
> --
> Hugo Kornelis, SQL Server MVP
|||Thanks
to you have any script for this operations - change collations for every
table / column
all in one
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%238MFa0vPGHA.2108@.TK2MSFTNGP10.phx.gbl...
> ... and if the existing object's collation also need to be changed, so
> ALTER TABLE ... ALTER COLUMN for each string column in each table.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
> news:drpe02pkevmct79b5nl8tfct2mm3c12io4@.4ax.com...
>
|||I haven't seen such script, but I haven't been looking for one either. Perhaps you can find if you
Google?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Tale" <tales@.eunet.yu> wrote in message news:duueuf$gst$1@.news.eunet.yu...
> Thanks
> to you have any script for this operations - change collations for every
> table / column
> all in one
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:%238MFa0vPGHA.2108@.TK2MSFTNGP10.phx.gbl...
>

Database collation

how to change database collation?On Mon, 27 Feb 2006 20:01:44 +0100, Tale wrote:

>how to change database collation?
>
Hi Tale,
ALTER DATABASE databasename
COLLATE collationname
Hugo Kornelis, SQL Server MVP|||... and if the existing object's collation also need to be changed, so ALTE
R TABLE ... ALTER COLUMN
for each string column in each table.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:drpe02pkevmct79b5nl8tfct2mm3c12io4@.
4ax.com...
> On Mon, 27 Feb 2006 20:01:44 +0100, Tale wrote:
>
> Hi Tale,
> ALTER DATABASE databasename
> COLLATE collationname
> --
> Hugo Kornelis, SQL Server MVP|||Thanks
to you have any script for this operations - change collations for every
table / column
all in one
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%238MFa0vPGHA.2108@.TK2MSFTNGP10.phx.gbl...
> ... and if the existing object's collation also need to be changed, so
> ALTER TABLE ... ALTER COLUMN for each string column in each table.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
> news:drpe02pkevmct79b5nl8tfct2mm3c12io4@.
4ax.com...
>|||I haven't seen such script, but I haven't been looking for one either. Perha
ps you can find if you
Google?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Tale" <tales@.eunet.yu> wrote in message news:duueuf$gst$1@.news.eunet.yu...
> Thanks
> to you have any script for this operations - change collations for every
> table / column
> all in one
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:%238MFa0vPGHA.2108@.TK2MSFTNGP10.phx.gbl...
>