change collation of the temp db

Question

change collation of the temp db

geoff hopkins

Ten Centuries

Points: 1147
More actions
February 15, 2006 at 4:34 am

#171141

Using MSDE2K
getting the error
'cannot resolve collation conflict for equal to operation'
when a stored procedure is being run on a new databse.... looking around it seems the problem is to do with the tempdb not having the same collation as the user databases..... joins using temp tables
can someone help me with how to change the collation of the tempdb to match the user databases ....
or
tell me how to install it in the first place correctly.... transferring the databases by using Backup Database.
thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ftu Valued Member Points: 69 More actions · Answer 1

Hello,

the tempdb database is created each time you start SQL Server and is based on master database collation. You need to change the collation of the master database and it should be ok.

geoff hopkins Ten Centuries Points: 1147 More actions · Answer 2

but how do you change the master collation to match the others.... I have no idea on commands or anything

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 3

Not sure if it changed from SQL2K to 2K5 but the 2K5 doco shows tempdb being based on model (like all other user DB's) (from BOL -> The tempdb database is built every time SQL Server is started and has the same default collation as the model database. )

If this holds true for SQL 2K, you should be able to modify model's collation.

Also, I haven't tested it but I thought with SQL2K to change the collation of master required a re-install?

Steve.

Michael Freidgeim SSC Journeyman Points: 79 More actions · Answer 4

I am trying to change model's collation

ALTER DATABASE model

COLLATE Cyrillic_General_CI_AS

and have an error

Server: Msg 3708, Level 16, State 5, Line 1

Cannot alter the database 'model' because it is a system database.

Could you suggest any workaround without re-install?

Ric Francis SSC Veteran Points: 206 More actions · Answer 5

I know this is from over a year ago, but I've hit the same problem myself

Anyway, the brutal fix for this is described here:

http://support.microsoft.com/kb/325335

...or reinstall SQL Server using the right collation.