Hi
I am having a problem with collation order mismatch when copying a set
of tables from one server to another. Each night I copy the live
database to a reporting server using a simple dts job that drops all the
tables and then rebuilds them and copies the data over - nice and simple
and has worked for the last two years.
I have built a new SQL 2000 server and set the collation order to
Latin1_General_CI_AS, this is the same as my other primary server,
except that the other server was upgraded from SQL 7 and it's collation
order is SQL_Latin1_General_CP1_CI_AS.
Now despite attempting to match the orders I cannot get the text to be
the same (although this should not make a difference since the real
collation order is the same), this is causing some of my applications to
fail as when performing some compares against a table created in
tempdb the system fails with a collation mismatch problem.
What happens is that the dts job keeps the original collation order on
the char and varchar columns, I have tried to modify the dts job so that
it ignores collation but haven't had any luck. So I'm hoping that
someone could give me some pointers?
I can of course copy over the data and then do an alter table script and
modify each column but I'd rather keep the dts job as simple as possible.
I have already rebuilt the new server with a number of different
collation orders to get it to match but I have the same issues each
time, I find it annoying that the collation compare is so stringent
since they are clearly the same collation order, only the text is different.

Any help would be appreciated.

Adrian Donlan