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

by

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.

More Related Contents:

  1. Check if table exists in SQL Server
  2. How do I create a unique constraint that also allows nulls?
  3. How do I get list of all tables in a database using TSQL?
  4. Get first day of week in SQL Server
  5. What is the significance of 1/1/1753 in SQL Server?
  6. Cannot truncate table because it is being referenced by a FOREIGN KEY constraint?
  7. Escape a string in SQL Server so that it is safe to use in LIKE expression
  8. Insert Update trigger how to determine if insert or update
  9. SQL time difference between two dates result in hh:mm:ss
  10. What is the best way to truncate a date in SQL Server?
  11. Dynamic SQL (passing table name as parameter)
  12. SQL Server: Difference between PARTITION BY and GROUP BY
  13. How do you truncate all tables in a database using TSQL?
  14. Understanding SQL Server LOCKS on SELECT queries
  15. SQL Server: How to select all days in a date range even if no data exists for some days
  16. SQL – The conversion of a varchar data type to a datetime data type resulted in an out-of-range value
  17. TSQL: How to convert local time to UTC? (SQL Server 2008)
  18. T-SQL Dynamic SQL and Temp Tables
  19. Dynamic SQL results into temp table in SQL Stored procedure
  20. Find non-ASCII characters in varchar columns using SQL Server
  21. How can I determine in SQL Server if a dateTime range overlaps another
  22. How do you change the datatype of a column in SQL Server?
  23. SQL Server Query – groupwise multiplication
  24. Getting week number off a date in MS SQL Server 2005?
  25. How to reuse calculated columns avoiding duplicating the sql statement
  26. How do I move a table into a schema in T-SQL
  27. How to use RANK() in SQL Server
  28. How to set value to variable using ‘execute’ in t-sql?
  29. comma delimited list as a single string, T-SQL [duplicate]
  30. Count number of records returned by group by

Leave a Comment