[b]Problems with Collations and Server Builds[/b]

[Nassy]

Hi

Hopefully someone can help me! My have just switched the server they use to store Sales data to a different box. Problem is that with the new build most of the fields in that database are set to the collation:

SQL1_Latin1_General_CP1_CI_AS

I need to join data from this new build to data that exists with in a new system on a different server. That server uses the default latin general collation.

Now that our sales data is on a new server I get a Collation Mismatch error every time I join to a field in the Sales data using the SQL Collation. This never happened when the Sales data was on the old server as then the collations on all the database fields were set to the default.

I am completely stuck. I have created a copy of the new sales database, set the collation the database and copied over the sales data from the new server into it, but the SQL collation on the fields stick

Any help mucho appreciated

Nassy

 

[JayKusch]

What version of SQL Server are you using?

Thanks

J. Kusch

 

[Nassy]

Thanks for the reply. I am using SQL 2000. I have now found a script which resets all the collations on a field level - it goes through each table on the copy of the sales database (CAL) and changes the collation on each field. It is a substantial piece of code though as it has to drop all the indexes, key constraints etc;any more info on collations and how they can get set at a field level when creating a new build would be extremely useful.

Nassy