SQL Server - Cannot resolve collation conflict for UNION operation

Ever tried to UNION two SELECT statement from tables/databases with different collations and hit the error: "Cannot resolve collation conflict for UNION operation"?

Me neither until today - managed to workaround it with help of the examples posted here:

DROP TABLE CollationTest1
CREATE TABLE [dbo].[CollationTest1](
     [CT1ID] [int] IDENTITY(1,1) NOT NULL,
     [CT1] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS
) ON [PRIMARY]

INSERT INTO CollationTest1 (CT1) VALUES ('varchar string 1 in CT1')
INSERT INTO CollationTest1 (CT1) VALUES ('varchar string 2 in CT1')
INSERT INTO CollationTest1 (CT1) VALUES ('varchar string 3 in CT1')
------------------------------------------------------------------------------
DROP TABLE CollationTest2
CREATE TABLE [dbo].[CollationTest2](
     [CT2ID] [int] IDENTITY(1,1) NOT NULL,
     [CT2] [varchar](30) COLLATE SQL_Latin1_General_Cp437_BIN
) ON [PRIMARY]

INSERT INTO CollationTest2 (CT2) VALUES ('varchar string 1 in CT2')
INSERT INTO CollationTest2 (CT2) VALUES ('varchar string 2 in CT2')
INSERT INTO CollationTest2 (CT2) VALUES ('varchar string 3 in CT2')
------------------------------------------------------------------------------
------------------------------------------------------------------------------
-- unmatched collation
SELECT CT1ID, CT1
FROM CollationTest1
UNION
SELECT CT2ID, CT2
FROM CollationTest2
/*
Msg 446, Level 16, State 9, Line 1
Cannot resolve collation conflict for UNION operation.
*/

-- unmatched collation
SELECT CT1ID, CT1, CT2
FROM CollationTest1
INNER JOIN CollationTest2 ON CT1ID = CT2ID
WHERE LEFT(CT1, 1) = LEFT(CT2, 1)
/*
Msg 446, Level 16, State 9, Line 1
Cannot resolve collation conflict for equal to operation.
*/

------------------------------------------------------------------------------
-- Force column CollationTest1.CT1 to match collation of CollationTest2.CT2
SELECT CT1ID, CT1 COLLATE SQL_Latin1_General_Cp437_BIN
FROM CollationTest1
UNION
SELECT CT2ID, CT2
FROM CollationTest2
/*
Results:
CT1ID   CT1
1               varchar string 1 in CT1
2               varchar string 2 in CT1
3               varchar string 3 in CT1
1               varchar string 1 in CT2
2               varchar string 2 in CT2
3               varchar string 3 in CT2
*/


-- Force column CollationTest2.CT2 to match collation of CollationTest1.CT1
SELECT CT1ID, CT1
FROM CollationTest1
UNION ALL
SELECT CT2ID, CT2 COLLATE SQL_Latin1_General_CP1_CI_AS
FROM CollationTest2
/*
Results:
CT1ID   CT1
1               varchar string 1 in CT1
2               varchar string 2 in CT1
3               varchar string 3 in CT1
1               varchar string 1 in CT2
2               varchar string 2 in CT2
3               varchar string 3 in CT2
*/


-- matched collation
SELECT CT1ID, CT1, CT2
FROM CollationTest1
INNER JOIN CollationTest2 ON CT1ID = CT2ID
WHERE LEFT(CT1 COLLATE SQL_Latin1_General_Cp437_BIN, 1) = LEFT(CT2, 1)
/*
Results:
CT1ID   CT1                                             CT2
1               varchar string 1 in CT1 varchar string 1 in CT2
2               varchar string 2 in CT1 varchar string 2 in CT2
3               varchar string 3 in CT1 varchar string 3 in CT2
*/


-- matched collation
SELECT CT1ID, CT1, CT2
FROM CollationTest1
INNER JOIN CollationTest2 ON CT1ID = CT2ID
WHERE LEFT(CT1, 1) = LEFT(CT2 COLLATE SQL_Latin1_General_CP1_CI_AS, 1)
/*
Results:
CT1ID   CT1                                             CT2
1               varchar string 1 in CT1 varchar string 1 in CT2
2               varchar string 2 in CT1 varchar string 2 in CT2
3               varchar string 3 in CT1 varchar string 3 in CT2
*/

I also discovered sp_help which is handy to query the collation of objects.