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 2000 Forums
 SQL Server Administration (2000)
 how to change Collate on the server

Author  Topic 

dejjan
Yak Posting Veteran

99 Posts

Posted - 2005-03-10 : 06:07:41
Hello everybody.

How could I change collate on the server. I know how it works when I want to change collate for database. But changing server collate is not that easy.
I would like not to reinstall SQL Server

Thanks in advanced

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-03-10 : 07:03:48
Look in Books Online under "Rebuild master".
Go to Top of Page

kish
Starting Member

45 Posts

Posted - 2005-03-10 : 08:01:08
You may want to check out this link to change the collation.
http://www.databasejournal.com/features/mssql/article.php/3302341

You can change the collation for a database or column level in SQL 2000 by using alter statements. You do not need to rebuild the master database.
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2005-03-10 : 13:18:06
The only time I ever did it I vowed if it ever happened again I would reinstalled the server ...

Up until then I would have shared you sentiment for not wanting to!

Kristen
Go to Top of Page

dejjan
Yak Posting Veteran

99 Posts

Posted - 2005-03-11 : 05:10:01
Thanks for advices.
Go to Top of Page

bakerjon
Posting Yak Master

145 Posts

Posted - 2005-03-16 : 17:15:53
I recently had to do this for a server (don't ask why). The interesting part of the problem was that the database held it's collation even though the server had a new, correct collation.

To change collation for the database to match the new server collation, I scripted out the tables (7.0 features only so not to get collation in the create tables), backed up the database and restored to with a different name ("with move"), dropped the old database, created a new one with proper collation, ran the script to create tables, and DTSed the data over from the copied DB to the new DB.

Make sense? It worked pretty well.

Jon Baker
quote:
Originally posted by dejjan

Thanks for advices.




What's with the yak thing?
Go to Top of Page

jason
Posting Yak Master

164 Posts

Posted - 2005-03-16 : 17:19:02
Why didn't you just change the collation of the DB with

ALTER DATABASE MyDatabase COLLATE <desired collation>

??
Go to Top of Page

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-03-16 : 19:24:21
changing collation settings at the server level is really no longer needed as jason has pointed out.



-ec
Go to Top of Page

cbr600f4
Starting Member

1 Post

Posted - 2005-04-06 : 06:18:12
Hi !

I have a somewhat similar problem, but I'm finding it a bit tougher to solve. I have a server on a specific collation, and the database has the same collation. Unfortunately, and to make a long story short, after I tinkered with the database to solve other problems, many columns ended up having a different collation (some of which with FKs) and are giving me problems (naturally....).

I there a way (tool maybe ?) that I can change the collation on those columns ? I thought about installing the same database schema (but all columns in the correct collation) through scripts in a test server with the right collation, and then using the "SQL Compare" tool to synchronize the schema of the problematic database. Any opinions on this ?

Thanks for any help !


Go to Top of Page

jonashilmersson
Starting Member

5 Posts

Posted - 2005-04-19 : 04:47:31
Hi!

I tried the ALTER DATABASE x COLLATE y with no errors, but when I then do a SELECT the sort order doesn't seem to be changed. To get the new sort order, I explictly need to use the COLLATE clause on my SELECT

select * from cor_addresses where description is not null order by description collate Latin1_General_CI_AS

I use nchar/nvarchar columns in my tables and the "description" column does not have any indexes.

Any tips?

Jonas

Go to Top of Page

thewyrme
Starting Member

1 Post

Posted - 2006-09-11 : 11:39:57
quote:
Originally posted by jonashilmersson


I use nchar/nvarchar columns in my tables and the "description" column does not have any indexes.

Any tips?



Wouldn't the nvarchar, nchar, and ntext data types be unicode, and therefore not subject to the collation settings defined for the standard string data?

There is no spoon.
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2006-09-11 : 11:43:08
"not subject to the collation settings defined for the standard string data"

Nope! Although you can store any character you want to in Unicode, at least in theory!, there is then the whole issue of the "equivalence" of Upper/Lower case, Accented characters etc. which effects sort order, comparison, and so on. The collation provides the rules for all of that.

Kristen
Go to Top of Page

Cardio
Starting Member

1 Post

Posted - 2007-11-04 : 15:52:23
watch out if you use temporary tables a lot and do joins to your databases.. temp tables are created in tempdb which always has the server default collation so if you are joining temp tables to your db tables you can get collation conflicts.. i.e. the server default does matter sometimes
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2007-11-04 : 17:10:50
"if you are joining temp tables to your db tables you can get collation conflicts"

We always EXPLICITLY state the Collate in ALL Table Create statements (DDL and also #Temp and @TableVar table definitions) which means we can move our databases to servers with different collations without having to worry about it.

(We use a server with different collation during QA to make sure we didn't miss any!)

Kristen
Go to Top of Page

supersql
Yak Posting Veteran

99 Posts

Posted - 2008-10-28 : 13:26:33
if its due ot temp tables then i nthat case can we change collation just for tempdb?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-28 : 13:28:30
just use COLLATE collation in joins.
Go to Top of Page
   

- Advertisement -