SQL Server accent sensitivity collation issue with except intersect operations

Question

I'm using SQL Server 2014 Enterprise. I have received a number of different databases from a client where I need to check tables, column names, number of records between databases. I've run into collation issues when using the Except function:

SELECT c.name "ColumnName"
FROM [DB1].sys.tables t
INNER JOIN [DB1].sys.all_columns c ON t.object_id = c.object_id
INNER JOIN [DB1].sys.types ty ON c.system_type_id = ty.system_type_id
WHERE t.name = 'test'

EXCEPT

SELECT  c.name
FROM [DB2].sys.tables t
INNER JOIN [DB2].sys.all_columns c ON t.object_id = c.object_id
INNER JOIN [DB2].sys.types ty ON c.system_type_id = ty.system_type_id
WHERE t.name = 'test2'

This generates the error:

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the EXCEPT operation.

FYI: DB1 is "SQL_Latin1_General_CP1_CI_AI", DB2 is "SQL_Latin1_General_CP1_CI_AS"

Is this something that can be resolved via the COLLATE function in a query such as mine? If so, how?

Also, what about the following that I found online:

USE master;
GO

ALTER DATABASE [DB2]
COLLATE SQL_Latin1_General_CP1_CI_AI ;
GO

I'll have to get our IT involved as I don't have permissions to do this.

1

Understood, thank you marc_s – Craig May 25 '17 at 08:31

score 2 · Accepted Answer · answered May 25 '17 at 05:27

Since you have values with different collations you should bring them to the same collation for comparison. One way to do it would be like this (decide which collation to use and use the same collation in both parts of EXCEPT):

SELECT  c.name COLLATE SQL_Latin1_General_CP1_CI_AS AS ColumnName
FROM 
    [DB1].sys.tables t
    INNER JOIN [DB1].sys.all_columns c ON t.object_id = c.object_id
    INNER JOIN [DB1].sys.types ty ON c.system_type_id = ty.system_type_id
WHERE t.name = 'test'

EXCEPT

SELECT  c.name COLLATE SQL_Latin1_General_CP1_CI_AS
FROM 
    [DB2].sys.tables t
    INNER JOIN [DB2].sys.all_columns c ON t.object_id = c.object_id
    INNER JOIN [DB2].sys.types ty ON c.system_type_id = ty.system_type_id
WHERE t.name = 'test2'

Changing the collation of the whole database (ALTER DATABASE [DB2] COLLATE SQL_Latin1_General_CP1_CI_AI;) would not help, because ALTER DATABASE doesn't change collation of existing columns, it would affect only default collation of new columns.

SQL Server accent sensitivity collation issue with except intersect operations

1 Answers1