?
Solved

Changing Default Collation

Posted on 2004-10-01
5
Medium Priority
?
591 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 100 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 100 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

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
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…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

719 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