Fixing Collation Conflict in SQL Server

The error: “cannot resolve collation conflict for equal to operation” and how we (partially) resolved the issues.

Background

Some time ago we had to replace a server running SQL Server 2000 after a hardware failure. All databases were restored onto the new server and everything had been running smoothly since.

However, an upgrade to our CMS software resulted in this error “cannot resolve collation conflict for equal to operation” in one SQL statement using a join, but only for some customers. It turned out these were the ones installed after the server switch. Further investigation showed that the new server had been installed with a default collation of SQL_Latin1_General_CP1_CI_AS, where the old server was Latin1_General_CI_AS.

Any new databases inherited their default collation from the server, but were created based on a template database using the previous collation. Any new fields added to the databases used the new collation, so we ended up with a mix. The SQL statement was attempting to join an older field to a new one using the different collation.

Solutions

There is a simple workaround that forces the join to use a specified collation, as below: –

SELECT * FROM table1 t1 INNER JOIN table2 t2 ON t1.field COLLATE database_default = tabl2.field COLLATE database_default

However, we wanted to fix up the databases as far as we could to avoid having to remember to us the workaround above, and to keep everything neat and tidy.

It is possible to reset the default collation for the server but this potentially difficult and lengthy process, so we decided to fix up the database defaults and the fields so taht they all used the previous collation.

The first step was to identify which databases were affected, which we did using the following: –

SELECT name, DATABASEPROPERTYEX( sysdatabases.name , ‘Collation’ ) FROM sysdatabases WHERE DATABASEPROPERTYEX( sysdatabases.name , ‘Collation’ )=’SQL_Latin1_General_CP1_CI_AS’ ORDER BY name

The second was to create SQL statements to alter the collection of any fields that had a different collation to the (fixed) default, as below: –

SELECT ‘ALTER TABLE [‘+USER_NAME(o.uid)+’].[‘+o.[name]+’] ALTER COLUMN [‘+c.[name]+’] ‘+
CASE
WHEN c.prec IS NULL THEN t.[name]
ELSE t.[name]+'(‘+CONVERT(varchar(5),c.prec)+’)’
END+’ COLLATE database_default’
FROM syscolumns c
JOIN sysobjects o ON (c.id = o.id)
JOIN systypes t ON (c.xusertype = t.xusertype)
WHERE c.collation IS NOT NULL
AND o.type = ‘U’ — NOT IN (‘P’,’FN’,’TF’,’IF’,’S’,’V’)
AND c.collation <> t.collation
ORDER BY o.[name]

The resulting SQL fixed up most of the fields but wouldn’t alter thos that were: –

• ntext
• part of a full-text index