SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 how to change Collate on the server
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dejjan
Yak Posting Veteran

99 Posts

Posted - 03/10/2005 :  06:07:41  Show Profile  Send dejjan a Yahoo! Message  Reply with Quote
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

USA
15676 Posts

Posted - 03/10/2005 :  07:03:48  Show Profile  Visit robvolk's Homepage  Reply with Quote
Look in Books Online under "Rebuild master".
Go to Top of Page

kish
Starting Member

45 Posts

Posted - 03/10/2005 :  08:01:08  Show Profile  Reply with Quote
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

United Kingdom
22415 Posts

Posted - 03/10/2005 :  13:18:06  Show Profile  Reply with Quote
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 - 03/11/2005 :  05:10:01  Show Profile  Send dejjan a Yahoo! Message  Reply with Quote
Thanks for advices.
Go to Top of Page

bakerjon
Posting Yak Master

USA
145 Posts

Posted - 03/16/2005 :  17:15:53  Show Profile  Visit bakerjon's Homepage  Reply with Quote
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 - 03/16/2005 :  17:19:02  Show Profile  Reply with Quote
Why didn't you just change the collation of the DB with

ALTER DATABASE MyDatabase COLLATE <desired collation>

??
Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 03/16/2005 :  19:24:21  Show Profile  Reply with Quote
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

Portugal
1 Posts

Posted - 04/06/2005 :  06:18:12  Show Profile  Visit cbr600f4's Homepage  Reply with Quote
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

Sweden
5 Posts

Posted - 04/19/2005 :  04:47:31  Show Profile  Reply with Quote
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 Posts

Posted - 09/11/2006 :  11:39:57  Show Profile  Reply with Quote
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

United Kingdom
22415 Posts

Posted - 09/11/2006 :  11:43:08  Show Profile  Reply with Quote
"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 Posts

Posted - 11/04/2007 :  15:52:23  Show Profile  Reply with Quote
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

United Kingdom
22415 Posts

Posted - 11/04/2007 :  17:10:50  Show Profile  Reply with Quote
"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 - 10/28/2008 :  13:26:33  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 10/28/2008 :  13:28:30  Show Profile  Reply with Quote
just use COLLATE collation in joins.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000