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:
Msg 457, Level 16, State 1, Line 1 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 "Latin1_General_BIN" in UNION ALL operator.
sp_serveroption
and set thecollation compatible
option to false for both server connections.HAVING COUNT(2) > 1
thisCOLLATE Latin1_General_CI_AS
on both placesCOLLATE
clause to a non-string expression..