changing collation

[Hulisi]

hi
i want to change SQL server collation properties.
How can i do it?
can you help me?

 

[hmckillop]

There isnt an easy way of doing this. Say your server has a collation of SQL_Latin1_General_CP1_CI_AS for the model database which is what tempdb gets built from I think, certainly tempdb has a collation of SQL_Latin1_General_CP1_CI_AS and you require a collation of Latin1_General_CI_AS, you would try and alster the collation of the actual model DB.
Firstly you can check this by
select databasepropertyex('tempdb', 'Collation')

How you fix the problem, well there are various options but the easiest may be a re-install of sql server. Unfortunately
ALTER DATABASE model COLLATE Latin1_General_CI_AS gives the error
--Cannot alter the database 'model' because it is a system database.


ALSO NOTE: The Setup program does not set the instance default collation to the Windows collation Latin1_General_CI_AS if the computer is using the U.S. English locale. Instead, it sets the instance default collation to the SQL collation SQL_Latin1_General_Cp1_CI_AS. This may change in a future release. - more of less admitting that this is a bug!

"I'm living so far beyond my income that we may almost be said to be living apart

 

[vongrunt]

This is tough process... save/export all data/schemas from all databases, run rebuildm utility, create/import all databases back.