SQL Error: Cannot resolve collation conflict for DISTINCT operation

I'd never run into this one before and am not likely to again, but the fix is pretty simple and worth posting for my own reference (and I hope it helps anyone googling for it too).

The problem occurs in SQL Server 2005 or SQL Server 2008 when you try and select a DISTINCT range of values from a sub-query that uses a UNION or UNION ALL to union two columns with different collations. The problem may occur if the collations are set differently at a database level while unioning two tables from different databases, and also at the column level within the same database. As with other "Cannot resolve collation conflict for XXX operation" errors, the fix is the same - convert the offending columns to the same collation using COLLATE DATABASE_DEFAULT:

--Step 1: create two similar tables with columns with different collations
DECLARE @temp1 TABLE (
    [Test1] NVARCHAR(100) COLLATE SQL_Latin1_General_Cp1250_CS_AS
)
DECLARE @temp2 TABLE (
    [Test2] NVARCHAR(100) COLLATE Icelandic_CS_AS
)

--Step 2: put in some values
INSERT INTO @temp1 VALUES(N'Falcon')
INSERT INTO @temp1 VALUES(N'Eagle')
INSERT INTO @temp1 VALUES(N'Tomcat')

INSERT INTO @temp2 VALUES(N'Mirage')
INSERT INTO @temp2 VALUES(N'Viggen')
INSERT INTO @temp2 VALUES(N'Harrier')

--Step 3: THIS WILL CAUSE A "Cannot resolve collation conflict for DISTINCT operation" ERROR
--Comment the following out to run step 4
--get distinct values from both tables, joined using UNION ALL in a subquery
SELECT DISTINCT [Test]
FROM (
      SELECT [Test] = [Test1] FROM @temp1
      UNION ALL
      SELECT [Test2] FROM @temp2
     ) I

--Step 4: the fix is to convert the columns to same collation using COLLATE DATABASE_DEFAULT
SELECT DISTINCT [Test]
FROM (
      SELECT [Test] = [Test1] COLLATE DATABASE_DEFAULT FROM @temp1
      UNION ALL
      SELECT [Test2] COLLATE DATABASE_DEFAULT FROM @temp2
     ) I
 

Tags: , ,

posted @ Friday, February 12, 2010 12:17 PM

Print

Comments on this entry:

# re: SQL Error: Cannot resolve collation conflict for DISTINCT operation

Left by Thomas at 2/17/2010 7:42 AM
Gravatar
The same error will most likely occur when joining two tables from different databases, e.g. a temporary table with an table from user database if the default database collation is different from the server default collation. This is usually the case when using non-american software. The solution is the same.

# re: SQL Error: Cannot resolve collation conflict for DISTINCT operation

Left by Thomas Williams at 2/18/2010 2:38 PM
Gravatar
Thanks Thomas - I've not yet not run into the non-US software situation (luckily), in my case it was 3rd-party software that specified a different collation on 1 column in their own database...I'm sure it was a mistake!
Comments have been closed on this topic.
«April»
SunMonTueWedThuFriSat
303112345
6789101112
13141516171819
20212223242526
27282930123
45678910