Can I change the master or tempdb Collation?

[jsteph]

Hi all,
I have an instance of sql-2005. All of the user db's were migrated from an sql-2000 instance, and they all have a collation of SQL_Latin1_General_CP1_CI_AS.

Yet the person who set up this new instance had set the instance up with the collation Latin1_General_CI_AI.

Because of this, I sometimes get collation errors and from what I've read, it's mostly due to sorting temp tables in the tempdb. So is is possible to change the tempdb collation, or is that based on the master or model db's collation? And if so, can I change either of those collations (without rebuilding all user dbs)?

Bottom line is I'm just looking for the easiest way to maintain the SQL_Latin1_General_CP1_CI_AS collation in my user db's and avoid the collation errors. What's the best way to achieve this?
Thanks,
--Jim

 

[SQLWilts]

Do you have any jobs/SSIS packages?

you may have to run rebuildm.exe to rebuild your master - you can then choose your collation.

collation problems are a real pain.

 

[jsteph]

SQLWilts,
Thanks very much. No jobs or ssis packages yet. I will look into the rebuildm.exe. Is that run from the windows command-line, or is there something in sms where that can be run from?
--Jim

 

[SQLWilts]

command line I'm afraid.

Oh, and you will need a CD/DVD with the correct version of SQL installation media on it - I forget where exactly, but rebuildm.exe copies the system DBs (except tempdb) from the CD/DVD to your install location.

Be aware that this will drop all attached DBs, lose all jobs and all logins - almost like a reload of SQL server but not quite.

Google it or BOL it and as long as this is a relatively new build, you should be OK.

As I said, changing
collations is a pain. Don't forget that from SQL 2000 onwards collation is effective from column level upwards

Good luck - if I can help further, give me a yell.

 

[gmmastros]

There are ways to handle temp tables so that the collation is not a problem.

Often times, table variables are faster than temp tables, so one solution is to use a table variable instead. Another way to accommodate this would be to specify the collation when creating the temp table.

Numbers are not affected by collation, but any string column is (char, nchar, varchar, nvarchar, text, ntext). If you decide to continue with temp tables (and sometimes that is the right thing to do), you can create the temp table using the same collation as the user table. Like this....

Create 
Table  #Temp(
       id int, 
       data varchar(20) [!]collate database_default[/!])

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLWilts]

George,

You are right - providing you have full access to the code! If it is an app supplied by a third party company, you may not have that luxury. Having gone through this pain (and I'm sure many a DBA has at one point or another during their career) I know that the only real way to solve this once and for all is to bite the bullet and change the system collation.

Also, although I havent played with them too extensively, I do know that table variables are also stored in tempdb - even thought they are slightly faster (proved to me at a MS conference recently - yes, I was surprised too!!). Given that, will not table vasriables hit the same problem?