When you have SQL Servers with different collation settings, your databases can be different on those servers, when you don't specify the collation explicitely. To solve this, you need to alter all columns with a different collation sequence. You have to do this manually, by opening each table in the SQL Enterprise Manager, reviewing each column, and change the collation sequence. It is needless to say, this is a tedious task.

The error message

Let's describe what happens if you don't use the same collation sequence within one database. When you compare two fields with a different collation setting (SELECT * FROM MyTable WHERE Fielda = FieldB), SQL Server will give you an error: Could not resolve collation conflict for equal to operation.

You will get this error only run-time, so you can't see it until you run your stored procedures, DTS tasks, or SELECT queries. In the worst case, you will not find out, but the users will. If this is the case, then you have a big problem!

The solution

I have written a bit of T-SQL code to generate ALTER TABLE statements. Each statement fixes the collation sequence to the same name. After executing this select statement, you need to review the output, and execute it in the Query Analyzer.

Here is the T-SQL code:
SELECT 'Alter Table [' + table_catalog + '].[' + table_schema + '].[' + table_name + '] alter column [' + column_name + '] ' + case data_type when 'char' then 'char' when 'varchar' then 'varchar' when 'text' then 'text' end + case when data_type <>'text' then '(' + convert(varchar,character_maximum_length) + ')' else '' end + ' collate SQL_Latin1_General_CP1_CI_AS ' + case when is_nullable='YES' then 'NULL ' else 'NOT NULL' end FROM INFORMATION_SCHEMA.COLUMNS WHERE collation_name <> 'SQL_Latin1_General_CP1_CI_AS' AND data_type in ('char', 'varchar', 'text') AND table_name in (select table_name from INFORMATION_SCHEMA.tables where table_type = 'BASE TABLE')

Notes

Before you use this code, read the following notes:

• Run this script on a specific database (first execute USE [database name] from the Query Analyzer)
• Change 'SQL_Latin1_General_CP1_CI_AS' to the desired collation name
• Set the output type of the Query Analyzer to Text, so you can easily copy the output
• First try the output of this script on a copy of your database
• Always make a backup of your database, before using Alter Table statements

Last modified on: 2009-02-05