Re: sql Cannot resolve collation conflict equals - comparing rows and fileds between Table1 and View1
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Wed, 27 Jun 2007 10:55:54 +0000 (UTC)
Yas (yasar1@xxxxxxxxx) 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@xxxxxxxxxxxxx
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
.
- References:
- Prev by Date: sql Cannot resolve collation conflict equals - comparing rows and fileds between Table1 and View1
- Next by Date: Parse field into multiple rows
- Previous by thread: sql Cannot resolve collation conflict equals - comparing rows and fileds between Table1 and View1
- Next by thread: Parse field into multiple rows
- Index(es):
Relevant Pages
|
