[dba-SQLServer] JOIN Issue with Databases

Michael,

Thanks for the resposne, I did see this just after I sent my note and oddly enough, the example Microsoft gives works as described but when I tried to use COLLATE SQL_Latin1_General_CP1_CI_AS on the appropriate field in either an INNER JOIN or the WHERE clause it returned an error stating that Collate was invalid.

THe workaround I used was to create a temp table collated appropriately, then dump everything from the source table into the newly collated temp and then join on that.  As it's a process that will run maybe once a day and with probably less than 100 records each day it should be fine.

-Chris Mackin

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com]On Behalf Of Michael
Maddison
Sent: Wednesday, September 29, 2004 6:34 PM
To: dba-sqlserver at databaseadvisors.com
Subject: RE: [dba-SQLServer] JOIN Issue with Databases


Something like this... from BOL

The predicate in the following query is evaluated in collation
greek_ci_as because the right expression has the explicit label, which
takes precedence over the implicit label of the right expression:

SELECT * 
FROM TestTab 
WHERE GreekCol = LatinCol COLLATE greek_ci_as


Looks like a real PITA
We had issues with this once when for some unknown reason the default
collations on 2 of our servers where
out of whack.  We were lucky that we could blow 1 away and rebuild with
correct collation.

cheers

Michael M



Hi,

I've got 2 business applications that use SQL Server 2000 databases, one
is:
 COLLATE SQL_Latin1_General_CP850_BIN
the other is:
 COLLATE SQL_Latin1_General_CP1_CI_AS

Trying to join data from tables in each database on a PO field
(Varchar(10)) results in the error:
Cannot resolve collation conflict for equal to operation.

Does anyone know a workaround for this?  The databases are required to
be Collated as is and I've tried modifying the join fields via Cast
INNER JOIN ({field} AS VarChar(10)) =  ({field} AS VarChar(10)) 

But this results in the same error.

Thanks,
Chris Mackin

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com