Peter,

According to BOL it can be done but requires as much work as reinstalling SQL Server itself. See Changing Collations in BOL for details.

>This has inherently 2 parts to this question regarding setting collation, and discrete collation settings, in an instance and a database in the instance and the consequences of having differential collation sequence settings between the instance and a database therein.
>
>I realise that, in SQL 2000 Enterprise Server, one can define an alternate collation sequence in the creation of a new database or table, etc., within any given instance regardless of the default collation sequence of the instance itself but ... and it's a big but ... is there a way to alter the default collation sequence of the INSTANCE AFTER installtion? I hope so <g> but I don't know how ... Can't find it in any reference materials. Can you help? Here's the situation:
>
>Using SQL 2000 Enterprise Server on a W2K Advanced Server:
>During installation, should have chosen:
>    Sort order '51'
>    Collation Name 'Dictionary order, case-sensitive, for use with 1252 character set.'
>to yield a default Instance collation sequence of: 'SQL_Latin1_General_Cp1_CS_AS'
>but inadvertently created the instance default of:           'Latin1_General_CS_AS'
>
>The difference is subtle and, theoretically should not present any problems; however even though the primary database has been set up as 'SQL_Latin1_General_Cp1_CS_AS' within this default of 'Latin1_General_CS_AS' there now appears to be a problem, that goes potentially to the matter of collation, with running certain stored procedure queries, including such queries as "SELECT DISTINCT .... " which is actually now not selecting 'distinctly' but produces a result set that includes a duplicate based upon the fact that there are 2 key fields used to match data from 2 tables in this database.
>
>However, the identical database running in a virtually identically set-up SQL instance running on another (older) server running W2K server OS (not Advanced version) and SQL 2000 Standard (not Enterprise version) but with both the instance and the database having exactly the same collation sequence of 'SQL_Latin1_General_Cp1_CS_AS', this same query runs correctly pulling only 1 record without also showing a duplicate. (The problem set-up - above - is a new set-up but the database in this new set-up is a RESTORE of a back-up copy of the original database on this other server and hence is identical; only the respective instance default collation sequence differs.)
>
>1) If difference in collation setting between the instance and the database is the likely source of the problem: how can I now change that default collation sequence of the instance to match the database (preferred option) without doing a complete re-install. And
>2) If difference in collation setting between the instance and the database is not the problem: what is likely to be the problem and how should we fix it? TIA. /psb