Search code examples
sqlsql-serverunioncollate

Collation conflict within the same database

I perform a union on two queries. Simplified version looks like this:

select * from Audit.dbo.PolicyList_t pl
union
select * from Audit.dbo.PolicyList_CurrencyFlip_t pl

Both tables are in the same database. However, I get the following error:

Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the UNION operation.

I would expect the collation error when operating on different databases not when querying two tables in the same database.

I checked the properties of both tables and in both cases, the collation is Latin1_General_CI_AS. However, when both selects address the same table I don't get any errors. Obviously, when running separately, both selects work.

I'm on SQL Server. Any suggestion what can be the source of the issue?

Solution

  • In SQL Server, at least, collation can be applied per column. So I'd check that.

    You might find this question about setting all columns to the same collation to be useful.