sql Cannot resolve collation conflict equals - comparing rows and fileds between Table1 and View1

Hello,

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.


Thank you!

Yas

Re: sql Cannot resolve collation conflict equals - comparing rows and fileds between Table1 and View

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/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx