Changing server collation order with existing databases

* 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
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
lissborgAuthor Commented:
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?
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.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

lissborgAuthor Commented:
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?

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?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
lissborgAuthor Commented:
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!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.