[dba-SQLServer]Collation SQL Challenge

Can anyone tell me an easy way to solve the following two sql statements?


--------------------------------------------------
DECLARE @table_a TABLE(THE_ID INT NOT NULL, THE_VALUE VARCHAR(20) Collate 
Latin1_General_CI_AS)
DECLARE @table_b TABLE(THE_ID INT NOT NULL, THE_VALUE VARCHAR(20) Collate 
SQL_Latin1_General_CP1_CI_AS)

SELECT CAST(THE_ID AS VARCHAR(8)) + THE_VALUE FROM @table_a
UNION
SELECT CAST(THE_ID AS VARCHAR(8)) + THE_VALUE FROM @table_b;

/* The above produces the following error message:
Server: Msg 446, Level 16, State 9, Line 4
Cannot resolve collation conflict for UNION operation.
*/
GO
--------------------------------------------------
DECLARE @table_c TABLE(THE_ID INT NOT NULL, THE_VALUE VARCHAR(20) Collate 
Latin1_General_CI_AS)
DECLARE @table_d TABLE(THE_ID INT NOT NULL, THE_VALUE VARCHAR(20) Collate 
SQL_Latin1_General_CP1_CI_AS)

SELECT CAST(THE_ID AS VARCHAR(8)) + THE_VALUE FROM @table_c
UNION ALL
SELECT CAST(THE_ID AS VARCHAR(8)) + THE_VALUE FROM @table_d;

/* The above produces the following error message:
Server: Msg 457, Level 16, State 1, Line 5
Implicit conversion of varchar value to varchar cannot be performed because 
the collation of the value is unresolved due to a collation conflict.
*/
GO
--------------------------------------------------

Thanks in advance,
Billy

_________________________________________________________________
MSN 8 with e-mail virus protection service: 2 months FREE*  
http://join.msn.com/?page=features/virus