Posted by Erland Sommarskog on 06/27/07 10:55

Yas (yasar1@gmail.com) writes:
> I currently have Table1 and View1.
>
> View1 is a query from 2 or 3 tables that works fine on its own.
>
> However in my current query if I try to use it...something like...
>
> SELECT a.col1, a.col2, a.col3, b.col1, b.col2, b.col3
> FROM View1 a JOIN Table1 b on a.col1 = b.col1
> WHERE a.col2 <> b.col2 OR a.col3 <> b.col3
>
>
> It throws an error "Server: Msg 446, Level 16, State 9, Line 1 Cannot
> resolve collation conflict for not equal to operation."
>
> Clearly I need to use collation between Table1 and View1, But I dont
> know where I need to use "COLLATE SQL_Latin1_General_CP850_CI_AI" and
> how? this is the collation set on Table1.

For instance:

SELECT a.col1, a.col2, a.col3, b.col1, b.col2, b.col3
FROM View1 a
JOIN Table1 b on a.col1 COLLATE SQL_Latin1_General_CP850_CI_AI = b.col1
WHERE a.col2 COLLATE SQL_Latin1_General_CP850_CI_AI <> b.col2
OR a.col3 COLLATE SQL_Latin1_General_CP850_CI_AI <> b.col3

You only need the COLLATE clause for character columns, so if any of these
are for instance int, you can skip the COLLATE clause for these.

What collation are the other columns? If they are of a different code
page than CP850 and you are using varchar, there could be unepxected results
due to character conversions.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

• Next in forum: Parse field into multiple rows
• Prev in forum: sql Cannot resolve collation conflict equals - comparing rows and fileds between Table1 and View1
• Thread view: Re: sql Cannot resolve collation conflict equals - comparing rows and fileds between Table1 and View1

[Reply to this message]