Solved

Changing Default Collation

Posted on 2004-10-01
5
584 Views
Last Modified: 2012-06-22
How can i change the default collation for my Production database Server.

Thanks

0
Comment
Question by:mostym
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12204535
There you go again, posting in the generic Database category and not mentioning which database brand/version you're using... :)

Judging from the question and your prior questions, I'm guessing you need this for SQL Server. If so, here's an example from SQL Server Books Online:

ALTER DATABASE MyDatabase COLLATE French_CI_AS
0
 
LVL 1

Assisted Solution

by:xabimond
xabimond earned 25 total points
ID: 12215810
In order to change the default collation for the server you need to rebuild the master database using the rebuildm utility.

You'll need to backup yours dbs before and recreate and restore or re-attach afterwards as the master holds the details of the other dbs on the server.

The databases default collations can be changed using ALTER DATABASE as above.
0
 

Author Comment

by:mostym
ID: 12218902
Thanks Guys,
Yeah my bad. It is SQL Server 2000.
I need to change the defaukt collation for my test database server to make it the same as my production server.
What r the risks involved if any..?

thanks a ton though... :-)

mostym
0
 
LVL 15

Accepted Solution

by:
jdlambert1 earned 25 total points
ID: 12219008
Of course it's always wise to do backups before system changes like this, even on a test server, and you should backup your master & msdb databases, in addition to your user databases. I hate to say there's little risk, because even if no one else ever had a problem with it before, it doesn't guarantee that you won't. Worst case, your database becomes unrecoverable (never heard of that happening from a change to collation), so you restore from backup. In that unlikely event, you could create a separate database, set it up to match your production system, then import the data from production.
0
 
LVL 1

Expert Comment

by:xabimond
ID: 12219955
If you need to change the collation for the server then you need to use rebuildm - have a look in BOL for how to run it.  There is no point backing up the masterdb as this will be recreated, but backup the others.

Once you've rebuilt the master - specifying the correct collation :-) - you can restore your other databases.  However, these will then be a different collation to the tempdb which can cause a collation conflict if you use temp tables in your app and join between your database and join temp table on a character colunmn.  You can change the default collation on a database, but this wont affect any existing columns.  To get around this recreate the db using a script - EM can generate one for you - without specifying the collation.  Then dts the data between old collation database and new default collation database.  If you don't use temp tables then you don't have to worry about this last bit!
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question