“default collation” for a SQL Server 2000 database

Some our clients reported  “’Cannot resolve collation conflict for equal to operation’” error. As it is explained here,  it could happen if the tempdb database collation differs from the user database collation.


I was curious where “default collation” for a SQL Server 2000 database is stored – in the database itself or in master system tables.


I didn’t find any good documentation for SQL Server 2000, but according to  SQL Server 2005 doc http://msdn2.microsoft.com/en-us/library/ms178534.aspx,


sys.databases view (corresponds to Master db ) has a column 









collation_name


sysname


Collation for the database. Acts as the default collation in the database.


NULL = Database is not online or AUTO_CLOSE is set to ON.


However I made a test


1.Created a new database with some Collation, different to what I have for my SQL instances.


2.Detached the database.


3. Attached the database to the another instance of SQL server.


The database default collation was the same, so it is stored in some database table, not in master database table.


BTW, unfortunetely, there is no way to change collation of tempdb without reinstalling the sql server (see discussion here