Re: Collation Conflict on Cross Database Join

From: Tibor Karaszi (tibor_please.no.email_karaszi_at_hotmail.nomail.com)
Date: 11/03/04 

Date: Wed, 3 Nov 2004 09:39:16 +0100

No need to create temp tables for this. What you need to do is to specify for one side of the JOIN
the collation so it matches the other side. Below is just an example to get you going on the syntax:

SELECT *
FROM titles AS t
 INNER JOIN publishers AS p ON t.pub_id = p.pub_id COLLATE Albanian_CI_AI 

-- 
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"CCA Dave" <david@notreally.co.uk> wrote in message news:cma4jn$k05$1$8302bc10@news.demon.co.uk...
> Hi all,
>
> I have the dubious please of 2 separate databases (the structures of which I
> cannot change), that I want to join for a simple query.
>
> select fields from
> table a inner join  table b
> on a.pk = b.pk
>
> However, the collation sequences on table a & b are different. Annoyingly,
> the data are the same. Can I avoid creating temp tables to join these
> together without getting:
> "Server: Msg 446, Level 16, State 9, Line 1
> Cannot resolve collation conflict for equal to operation."
>
> Any ideas?
>
>

Relevant Pages

  • Re: Collation advice going from 2000 to 2008
    ... it's more faster to change the actual create temp table statements ... than to change the existing collation of a database or to adapt the JOIN ... Sylvain Lafontaine, ing. ...
    (microsoft.public.sqlserver.server)
  • Re: Is it possible to change tempdb? a collation problem
    ... We have a similar issue on some systems where the collation at installation ... has been incorrectly set relative to user databases, causing join errors ... with temp tables. ...
    (microsoft.public.sqlserver.server)
  • Re: Collation advice going from 2000 to 2008
    ... if you have trouble with a temp table and a collation when making ... Sylvain Lafontaine, ing. ... MVP - Windows Live Platform ...
    (microsoft.public.sqlserver.server)
  • Re: Collation advice going from 2000 to 2008
    ... Have you ever tried migrating a db with one collation onto a server ... if you have trouble with a temp table and a collation when making ... Sylvain Lafontaine, ing. ...
    (microsoft.public.sqlserver.server)
  • Re: sysindexes
    ... collation, but you should be able to use it for your column change as well. ... SQL Server MVP ... DECLARE @sql VARCHAR ... INNER JOIN information_schema.table_constraints tc ...
    (microsoft.public.sqlserver.server)