4

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.

5
  • And what is the collation of the local server (where the query is executed)? Oct 21, 2015 at 10:44
  • That is SQL_Latin1_General_CP1_CI_AS
    – Tom
    Oct 21, 2015 at 10:48
  • I think you should check the sp_serveroption and set the collation compatible option to false for both server connections. Oct 21, 2015 at 10:51
  • write after HAVING COUNT(2) > 1 this COLLATE Latin1_General_CI_AS on both places
    – wiretext
    Oct 21, 2015 at 11:23
  • 1
    @tinka That would cause an error since you cannot add a COLLATE clause to a non-string expression.. Oct 21, 2015 at 11:53

1 Answer 1

3

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.

12
  • 2
    They claimed "I have tried adding COLLATE SQL_Latin1_General_CP1_CI_AS to the column names". +1 for guessing they didn't apply this to the column with the fixed value ;) Oct 21, 2015 at 11:58
  • @ypercube I did actually, and I am still getting the same error am afraid. If I add the afore mentioned line to this and the columns I get a conflict against Latin1_General_BIN
    – Tom
    Oct 21, 2015 at 12:44
  • Have done as requested
    – Tom
    Oct 21, 2015 at 12:55
  • 1
    @Tom you added COLLATE only to the columns of the 2nd part of the UNION and not the 1st part. This agrees with the message you get ("Latin1_General_CI_AS" and "Latin1_General_BIN"). You already said that all the columns have Latin1_General_BIN collation. Oct 21, 2015 at 12:59
  • 1
    @ypercube Please see my update. Even though it does work, it is unnecessary to add the COLLATE clause to both queries. It was mistakenly added to the resourcephone.[PhoneExt] field when it shouldn't have been, hence fixing one error and causing the new one. The real fix is to remove the extraneous COLLATE rather than add yet another. Oct 21, 2015 at 13:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.