I have a large (ish) number of databases that various people where I
work have created over the last few years. The problem is that no-one
here really understands collations and a mistake made originally keeps
cropping up and causing problems depending on where the create table
script has come from.

What happens is that someone needs to create a new database (new
client) and they pick an existing one, create the script (often
forgetting indexing, and they don't understand that either) and off
they go with a new copy.

Most of the time we get away with it, but when writing various things
I get errors about the conversion not being able to be performed.

I can change the scripts to forcibly use a certain collation, but
that's the wrong approach as it may impact other things down the line.
It would also mean re-writing a fair chunk of SP's and views etc...
With the added problem that this may be different from one database to
another. The database could be in one collation and tables could
either be the same, or a different one entirely and not consistent
between copies for different clients. In short, it's in a mess.

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 ?

Thanks in advance


Ryan

Should have mentioned, it's SQL2000

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/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx