Changing Servers Collation

Personally I would go for the first, as its the bol
recommendation.
Out of curiousity have you thought of going down the
unicode route ?
J

>--Original Message--
>I am considering two solutions for changing the collation
on a Server, the first is to follow the advise of BOL and
script all the user DB's, export the data to text files or
another media and having deleted these databases from the
server use the Rebuildm.exe utility to reinstall the
Master database with the new collation. Then run the
scripts to recreate the DB's and import the data back in
>The second is to script the dropping and re-creation of
all indexes and keys and then change the column collations
using the following script to generate the alter table
statements required and then change the database
collation. Once this is done I can detach the user
databases and use the Rebuildm.exe utility, then I simply
re-attach the DB's.
>I have 75 user databases on the Server in question, any
suggestions on which method would be better.
>many thanks
>Ed
>select x =
> case so.xtype
> when 'u' then 'alter table ' + so.name
+ ' alter column ' + sc.name + ' ' + st.name + '('
> + cast(sc.length as varchar(5))
+ ') COLLATE Latin1_General_CI_AS;'
> END
>from syscolumns sc, sysobjects so, systypes st
>where so.id = sc.id
>and sc.xtype = st.xusertype
>and so.xtype = 'u'
>and so.name <> 'dtproperties'
>and st.xusertype in (175, 239, 99, 231, 35, 167)
>and sc.collation = 'SQL_Latin1_General_CP1_CI_AS'
>.
>Unfortunatly I have inherited a large number of databases with tables that h
ave a row width coming close to or excreding the 8000 charcter limit and yes
I have tried repeatedly to get this sorted out by normalising the tables bu
t its not to be.