Sql – collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “SQL_Latin1_General_CP1_CS_AS” in the equal to operation

collationinner-joinsql

This is my code

    SELECT  
                 CASE j.ANALYSIS  
                       WHEN  'INSTALL'  THEN 'ProtectedProperty'   
                       WHEN  'REMOVAL' THEN 'UnProtectedProperty'  
                 END   AS  label ,  
                       Count(P.PROPERTYREF) AS value   
                 FROM                                                   
                       properties AS p   WITH (NOLOCK)                                      
                 INNER JOIN           
                       jobs AS j  WITH (NOLOCK)
                 ON 
                       j.PROPERTYREF = p.PROPERTYREF          
                 INNER JOIN                                      
                       LIVE_PROPS AS lp   WITH (NOLOCK)           
                 ON  
                       j.PROPERTYREF =lp.PROPERTYREF                   
                 WHERE              
                       j.Completed BETWEEN @dtmStartTime AND @dtmEndTime                  
                 AND             
                       p.OFFICE In (Select OfficeId FROM tempTbl)

Got an error like
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

Best Answer

The collation is different between two of your columns. Add COLLATE Latin1_General_CI_AS after the offending equal to operation.

For example, if the problem was the LIVE_PROPS table:

INNER JOIN LIVE_PROPS AS lp WITH (NOLOCK)
     ON j.POPERTYREF = lp.PROPERTYREF COLLATE Latin1_General_CI_AS

Related Solutions

Sql – the difference between “INNER JOIN” and “OUTER JOIN”

Assuming you're joining on columns with no duplicates, which is a very common case:

An inner join of A and B gives the result of A intersect B, i.e. the inner part of a Venn diagram intersection.
An outer join of A and B gives the results of A union B, i.e. the outer parts of a Venn diagram union.

Examples

Suppose you have two tables, with a single column each, and data as follows:

Note that (1,2) are unique to A, (3,4) are common, and (5,6) are unique to B.

Inner join

An inner join using either of the equivalent queries gives the intersection of the two tables, i.e. the two rows they have in common.

select * from a INNER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a = b.b;

a | b
--+--
3 | 3
4 | 4

Left outer join

A left outer join will give all rows in A, plus any common rows in B.

select * from a LEFT OUTER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a = b.b(+);

a |  b
--+-----
1 | null
2 | null
3 |    3
4 |    4

Right outer join

A right outer join will give all rows in B, plus any common rows in A.

select * from a RIGHT OUTER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a(+) = b.b;

a    |  b
-----+----
3    |  3
4    |  4
null |  5
null |  6

Full outer join

A full outer join will give you the union of A and B, i.e. all the rows in A and all the rows in B. If something in A doesn't have a corresponding datum in B, then the B portion is null, and vice versa.

select * from a FULL OUTER JOIN b on a.a = b.b;

 a   |  b
-----+-----
   1 | null
   2 | null
   3 |    3
   4 |    4
null |    6
null |    5

Sql – the difference between UNION and UNION ALL

UNION removes duplicate records (where all columns in the results are the same), UNION ALL does not.

There is a performance hit when using UNION instead of UNION ALL, since the database server must do additional work to remove the duplicate rows, but usually you do not want the duplicates (especially when developing reports).

To identify duplicates, records must be comparable types as well as compatible types. This will depend on the SQL system. For example the system may truncate all long text fields to make short text fields for comparison (MS Jet), or may refuse to compare binary fields (ORACLE)

UNION Example:

SELECT 'foo' AS bar UNION SELECT 'foo' AS bar

Result:

+-----+
| bar |
+-----+
| foo |
+-----+
1 row in set (0.00 sec)

UNION ALL example:

SELECT 'foo' AS bar UNION ALL SELECT 'foo' AS bar