[dba-SQLServer]Collation SQL Challenge

In the first snippet, rewrite your UNION by specifying the COLLATE for either the first or the second part of the UNION:

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

Exactly the same concept applies to your second snippet.

Robert Djabarov
Senior SQL Server DBA
USAA IT/DBMS
? (210) 913-3148 - phone
? (210) 753-3148 - pager


 -----Original Message-----
From: 	Billy Pang [mailto:tuxedo_man at hotmail.com] 
Sent:	Monday, July 07, 2003 11:27 AM
To:	dba-SQLServer at databaseadvisors.com
Subject:	[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

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com