SQL Server error "Implicit conversion of because the collation of the value is unresolved due to a collation conflict."

Question

I am getting this error while developing a stored procedure

Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict.

statement is like this

Select City COLLATE DATABASE_DEFAULT AS Place, State, Country FROM DEPT1
UNION ALL
Select '' AS Place, 'Arizona' As State, Country FROM DEPT2

but If If do this it also give same error

 Select City COLLATE DATABASE_DEFAULT AS Place, State, Country FROM DEPT1
 UNION ALL
 Select '' COLLATE DATABASE_DEFAULT AS Place, 'Arizona' As State, Country FROM DEPT2

Actually this code is written by some one else and am just editing the code, do not know why he added COLLATE DATABASE_DEFAULT but If I remove it also gives the same error

Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict.

Answer 1

You'd need COLLATE in both places most likely.

Select City COLLATE DATABASE_DEFAULT AS Place, State, Country FROM DEPT1
UNION ALL
Select '' COLLATE DATABASE_DEFAULT AS Place, 'Arizona' As State, Country FROM DEPT2

Edit: You may need it on every string if you get it in one places

Select
    City COLLATE DATABASE_DEFAULT AS Place,
    State COLLATE DATABASE_DEFAULT AS State,
    Country COLLATE DATABASE_DEFAULT AS Country
FROM DEPT1
UNION ALL
Select
    '' COLLATE DATABASE_DEFAULT,
    'Arizona' COLLATE DATABASE_DEFAULT ,
    Country COLLATE DATABASE_DEFAULT
FROM DEPT2

Edit2:

It happens because your column collation is probably different to your database collation. So "City" has one collation but string constants have another.

Answer 2

ALTER TABLE DEPT1 and DEPT2 so that resulting definitions of tables do not contain any COLLATE