August 28, 2002 at 7:00 pm
Can I change the collation of tempdb? I get the following error message when I run the alter database statement to change the collation:
Server: Msg 3708, Level 16, State 5, Line 1
Cannot alter the database 'tempdb' because it is a system database.
Is there a way around this? Thank you.
August 28, 2002 at 7:27 pm
No, not without a reinstall.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
August 28, 2002 at 11:25 pm
TempDB is rebuilt each time SQL Server is started. As a result, it's going to use the collation of the server.
You can alter the collation of the server by using rebuildm.exe. However, this effectively rebuilds the master, msdb, and model databases. You'll need to get your user databases, jobs, etc. out before running the command, but it is slightly faster than doing a reinstall because you don't have to go through installing the binaries, etc. There's more information in Books Online about using rebuildm.exe.
Out of curiousity, what is the need for changing the collation on tempdb?
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
August 29, 2002 at 3:27 am
Brian gave much better detail. However let me add one point. If this server is SQL 2000 you can add the user databases back without issue as they can have their own collation. If this is 7 you need to move the databases to another server or bcp out the data and script the objects as you will have to build new databases under the new collation as SQL 7 databases all use server default collation setting.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply