July 7, 2003 at 10:08 am
Can anyone tell me a good 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
Edited by - bp on 07/07/2003 10:29:05 AM
July 7, 2003 at 9:25 pm
In a union the columns returned must contain data of a single datatype. This will be decided by the first statement in the union.
If the server cannot implicitely convert all elements to the datatype you will get an error.
The union will try to get distinct values by the default collation whereas the union all will try to convert the second part of the union (and the default collation from the ID convert) to Latin1_General_CI_AS which is why you get different errors.
The only way out of this is to explicitely convert all elements to the collation you want for the result.
Cursors never.
DTS - only when needed and never to control.
Cursors never.
DTS - only when needed and never to control.
July 8, 2003 at 6:59 am
Following on from Nigel's post, your first SELECT could be coded as:
SELECT CAST(THE_ID AS VARCHAR(8)) + THE_VALUE COLLATE DATABASE_DEFAULT FROM @table_a
UNION
SELECT CAST(THE_ID AS VARCHAR(8)) + THE_VALUE COLLATE DATABASE_DEFAULT FROM @table_b;
Cheers,
- Mark
June 4, 2008 at 3:25 am
Hi,
i was getting same error. The solution is when you select each field you need to specify default database collate as mark said.
e.g.
select field1 Collate database_default, field2 Collate database_default
from table x
Union
select field1 Collate database_default, field2 Collate database_default
from table y
it will solve your problem. at least it did mine.
regards,
vijay
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply