Posted by Erland Sommarskog on 02/01/07 22:20

Ryan (ryanofford@hotmail.com) writes:
> The two collations in question are :
>
> Latin1_General_CI_AS and
> SQL_Latin1_General_CP1_CI_AS
>
> My ideal way of solving this would be to export the data somewhere and
> re-create the entire db with a proper script and then import the data
> again. Is this a sensible option ? What options are available to me
> and what is the best way of correcting this ? Is there a quick way of
> doing this ?

You can use ALTER TABLE ALTER COLUMN to change the collation of each
column. Those commands can easilly be created by running a query against
syscolumns. However, you cannot change the columns on indexed columns,
so you need to drop all indexes, including primary keys. And to be able to
drop primary keys, you must also drop referring foreign keys. And then
restore these once you're done.

So in the end, it may be better to build an empty database from scripts,
preferrably taken from version-control, but if there is no such thing,
you would have to generate scrtips. Whatever, make sure that the script
has no COLLATE clauses at all, and that the new database uses the server
collation.

While you could bulk-in and bulk-out, you could also move the data
by means of INSERT. In this case, it would be better to disable foreign
keys, and then reenable them once your done.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]