Cannot resolve collation conflict for equal to operation

[Warnie]

Hi,

I am stuck in a problem. My application database was created for the collation setting SQL-Latin1_General_CP1_CI_AS. We have built an install shield for this which will attach the databases during the installation. This works fine as long as the server's collation setting is SQL_Latin1_General_CP1_CI_AS.

Now we have a showstopper. When the machine's regional setting is set as English(United Kingdom), the collation setting is taken as Latin1_General_CI_AS. Our install shield sets up the application successfully.But when we try to login to the application, we are confronted with a SQL error "Cannot resolve collation conflict for equal to operation". On further investigation, we found that this is happening due to collation settings being different on the installation machine. The machine is running XP. How do I handle this? We have also tried to force the collation settings of our application databases as SQL_Latin1_General_CP1_CI_AS. Though our application databases have taken this collation setting, we are still unable to login. Please suggest a way out.

Thanks,
Warnie

 

[SonOfEmidec1100]

This has caused me more grief than anything else with migrating from SQL7 to SQL2000. Here are some comments maybe someone else has other options....

The main problem is you cannot change collation seq of an existing database by attatching, restoring from backup etc.
I have found no easy solution, here are three I have considered - I only used option 2 when 3 was not possible, 1 has never been tried. There is a side effect of option 2 with the specific collations you mention sort fields containing certain characters get sorted differently eg minus signs get ignored by Latin1_General_CI_AS AB and A-B are sorted equal.

1) modify all sql queries doing string matching to include a collation override (or words to that effect)

2) Create a script to build the database schema and under formatting make sure to check - Only script sql 7.0 compatible features. The use DTS to transfer the data being carefull about which options you select - such as do not create destination objects.

3) Reinstall sql server on the target machine select custom options and force the collation seq to be as required - I did this wherever possible.

Hope this helps a little.