COLLATION and UNION ALL

I have the following queries:

SELECT  *
FROM    OPENQUERY([SERVERA],
    'SELECT ''SERVERA'',[PhoneNum]
    ,[PhoneExt]
    ,COUNT(2) as CNT
    FROM [DB].[dbo].[ResourcePhone] WHERE PhoneNum like ''+44%''
    OR PhoneNum like ''+41%''
    GROUP BY PhoneNum, PhoneExt
    HAVING COUNT(2) > 1;')
        UNION ALL
SELECT  *
FROM    OPENQUERY([SERVERB],
    'SELECT ''SERVERA'',[PhoneNum]
    ,[PhoneExt]
    ,COUNT(2) as CNT
    FROM [DB].[dbo].[ResourcePhone] WHERE PhoneNum like ''+44%''
    OR PhoneNum like ''+41%''
    GROUP BY PhoneNum, PhoneExt
    HAVING COUNT(2) > 1;')

I am getting the error Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in UNION ALL operator.

I have checked the collation of the columns and database involved and they are all Latin1_General_BIN. The difference is SERVERA has a collation of SQL_Latin1_General_CP1_CI_AS and SERVERB has a collation of Latin1_General_CI_AS. I have tried adding COLLATE SQL_Latin1_General_CP1_CI_AS to the column names but this has not worked.

Can anyone advise how to resolve this?

Added exact query

SELECT  *
FROM    OPENQUERY([SERVERA],
    'SELECT ''SERVERA'',resourcephone.[PhoneNum]
    ,resourcephone.[PhoneExt]
    ,COUNT(2) as CNT
    FROM [rtc].[dbo].[ResourcePhone] WHERE PhoneNum like ''+44%''
    OR PhoneNum like ''+41%''
    GROUP BY PhoneNum, PhoneExt
    HAVING COUNT(2) > 1;')
        UNION ALL
SELECT  *
FROM    OPENQUERY([SERVERB],
    'SELECT ''SERVERB'' COLLATE Latin1_General_CI_AS, resourcephone.[PhoneNum] COLLATE Latin1_General_CI_AS
    ,resourcephone.[PhoneExt] COLLATE Latin1_General_CI_AS
    ,COUNT(2) as CNT
    FROM [rtc].[dbo].[ResourcePhone] WHERE PhoneNum  like ''+44%''
    OR PhoneNum like ''+41%''
    GROUP BY PhoneNum, PhoneExt 
    HAVING COUNT(2) > 1;')

This query gives the following message:

Ms

The problem should be with the string literal (i.e. the ''SERVERA'') as that takes on the Collation of the database where that statement is executing.

So try the following for the top part of your query:

SELECT *
FROM OPENQUERY([SERVERA],
'SELECT ''SERVERA'' COLLATE Latin1_General_CI_AS, [PhoneNum]
...


And, the bottom part of the UNION ALL (the part for [SERVERB]) should probably select "SERVERB" instead of "SERVERA" as the string literal :).

And to be technical, it is taking on the Collation of the DB, not the server, though if the default connection is to master (or any system DB), then that would naturally be the same as the Server-level Collation since the Server-level Collation is used to create the system DBs.

UPDATE:

The new error you are getting after adding the COLLATE clause to the 2nd query in the UNION ALL is due to adding the COLLATE clause to too many of the fields. It needs to go only to the literal string, and not on the resourcephone.[PhoneExt] field. Doing that one extra step put the fields that were already in agreement into conflict. Since both of those table fields are in Latin1_General_BIN, there is no need to override the Collation on the resourcephone.[PhoneExt] field from either Server. It is only the string literal "field" that needs to be overriden, and only from one of the two servers: you just need to make one match the other.

Adding the COLLATE clause to all fields, especially ones that are already working as expected (i.e. the resourcephone.[PhoneExt] field) is unnecessary and is over-complicating the query.

StackExchange Database Administrators Q#118723, answer score: 3