Posted by Ryan on 02/01/07 15:56

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

[Back to original message]