How to solve "Cannot resolve the collation conflict between X and Y" error

Steps to reproduce

When executing a query you see an error that looks like this:

Cannot resolve the collation conflict between "SQL_Latin1_X" and "SQL_Latin1_Y" in the concat operation

When you execute the same query directly on your database, it works fine.

Problem

The collation of your external DB and your own DB do not match.

Solution

If you are using a MSSQL database, Atlassian has an article on how to fix this: https://confluence.atlassian.com/jirakb/how-to-fix-the-collation-of-a-microsoft-sql-server-jira-database-776646810.html

You could also add COLLATE statements to your query, as described here: https://stackoverflow.com/questions/23673096/use-collate-in-concat