| 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". |
 |
|
|
kish
Starting Member
45 Posts |
|
|
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 |
|
|
|
dejjan
Yak Posting Veteran
99 Posts |
Posted - 2005-03-11 : 05:10:01
|
| Thanks for advices. |
|
|
|
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 Bakerquote:
Originally posted by dejjan
Thanks for advices.
What's with the yak thing? |
|
|
|
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>?? |
|
|
|
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 |
|
|
|
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 ! |
|
|
|
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 SELECTselect * 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 |
|
|
|
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. |
|
|
|
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 |
|
|
|
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 |
|
|
|
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 |
|
|
|
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? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-28 : 13:28:30
|
| just use COLLATE collation in joins. |
|
|
|
|
how to change Collate on the server