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?
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.