[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Changing server collation order with existing databases

Posted on 2004-11-05
Medium Priority
Last Modified: 2008-01-09
* An existing SQL-server with a lot of databases
* Server collation order: SQL_Latin1...

I need to install a new databas that must use Finnish_Swedish collation. I also need the server collation to be the same since it uses a lot of temporary tables (that will use server default as collation). Due to the size and complexity of the databas it's not an option to rewrite all SP and SQL-code so that the collation problem is taken care of in every sql-statement involving temporary tables.

Is it possible to change the server collation and preserving the installed databases?
If so, how?

Hans Lissborg
Question by:lissborg
  • 3
  • 3
LVL 18

Expert Comment

ID: 12503564
temp db is recreated each time SQL server is stopped and restarted.   It is based on the model database.    change the collation of model database and stop and start sql then temp db should take on your new collation.   but be careful that this doesnt break all the existing databases.

IMHO if the other databases are using tempdb for temptables also its worth simply installing another instance of SQL.

Author Comment

ID: 12503648
I nice solution, but when I try to change the collation on database Model with the alter database Model COLLATE command I get the error:

Server: Msg 3708, Level 16, State 5, Line 1
Cannot alter the database 'Model' because it is a system database.

Any workaround?
LVL 18

Expert Comment

ID: 12503722
Hmm.  sorry forgot about that little fact.

Ok.   there are 2 options i can think of.   1) rebuild your master database this will allow you to change the server collation. or 2) install a second instance.
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 12503786
OK, That what I guessed.

If I choose option 1 (rebuliding master) then I must first backup (and close) all my databases, run rebuildm.exe, and then restor all databases. Is that right?

If I choos option 2 (new instance) do I then need another licence om sql Server 2000?

LVL 18

Accepted Solution

ShogunWade earned 2000 total points
ID: 12504028
option 1)  it would be quicker to detatch  the databases then simply reattach after install.   but for safety back them up anyway.

2) what type of licence to you have, per seat of per server?

Author Comment

ID: 12504065
ok, I didn't thought it was ok to detatch/reattach after changing collation in master but maybe you´re right.

It's a customers server so I´m not shure but I think I go for option 1.

Thank you!

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

868 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