SQL Is it possible to collate twice?

Question

Hello I have created the following script, the problem I am running into is that the two tables I am querying are collated differently, I tried to insert the data into a third table, but because of the collation of the tables I cannot do this, I also cannot collate the tables themselves due to limitations on the database and the program that relies upon it. So is it possible to collate this correctly?

Error:    Msg 468, Level 16, State 9, Line 69
    Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.





        --Create Table #tmp2(FK_clientids varchar(50))
        --Create table #tmp (phonenums varchar(50))
        --Create table #tmp3 (phonenum varchar(50),fk_applicationid_solref varchar(50),calldata varchar(50),sourcetable varchar(50))
        Delete from #tmp2
        Delete from #tmp
        Use DATABASE2
        INSERT INTO #tmp2
        SELECT fk_clientid
        FROM DM_ClientApplicants
        where FK_ApplicationID in (39591,
        39594,
        39598,
        39596,
        39600,
        39601,
        39603,
        39609,
        39613,
        39585,
        39560)

        Use DATABASE2
        INSERT INTO #tmp
        Select phonenum2 from DM_PhoneNumbers
        where FK_ApplicationID in 
        (
           Select FK_clientIDs from #tmp2
        )
        INSERT INTO #tmp
        Select PhoneNum1 from DM_PhoneNumbers
        where FK_ApplicationID in 
        (
           Select FK_clientIDs from #tmp2
        )
        INSERT INTO #tmp
        Select PhoneNum2 from DM_PhoneNumbers
        where FK_ApplicationID in 
        (
           Select FK_clientIDs from #tmp2
        )

        INSERT INTO #tmp
        Select PhoneNum3 from DM_PhoneNumbers
        where FK_ApplicationID in 
        (
           Select FK_clientIDs from #tmp2
        )
        INSERT INTO #tmp
        Select Partnerphonehome from DM_PhoneNumbers
        where FK_ApplicationID in 
        (
           Select FK_clientIDs from #tmp2
        )
        INSERT INTO #tmp
        Select Partnerphonemobile from DM_PhoneNumbers
        where FK_ApplicationID in 
        (
           Select FK_clientIDs from #tmp2
        )
        INSERT INTO #tmp
        Select Partnerphonework from DM_PhoneNumbers
        where FK_ApplicationID in 
        (
           Select FK_clientIDs from #tmp2
        )
        Use DATABASE1
        insert into #tmp3

Select sourcetable,CallData,PhoneNum,P.FK_ApplicationID from Dial D

        join DATABASE2.dbo.DM_PhoneNumbers P on PhoneNum = PhoneNum1
        join DATABASE2.dbo.DM_Sol s on S.FK_ApplicationID = P.FK_ApplicationID
       Collate Latin_general_CI_AS

        where PhoneNum in
        (
        Select phonenums from #tmp
        )

Can't you show us "Line 69", so we easily can understand what's going on?!? — jarlh, Apr 07 '15 at 15:01
join DATABASE2.dbo.DM_PhoneNumbers P on PhoneNum = PhoneNum1 — Denslat, Apr 07 '15 at 15:07

score 1 · Accepted Answer · answered Apr 07 '15 at 15:46

1

You can use a collate clause as part of your ON clause. From your posted code, I cant' tell which is which, but it will be something like this:

    join DATABASE2.dbo.DM_PhoneNumbers P 
            on PhoneNum collate SQL_Latin1_General_CP1_CI_AS  = PhoneNum1

The point being you need to use the collate to modify the collation of one of the join columns to match the other one.

answered Apr 07 '15 at 15:46

Andrew

8,445
3
28
46

As I said, I can't tell how either table is collated. Try using `Latin1_General_CI_AS` instead. – Andrew Apr 07 '15 at 15:49
disregard previous comment – Denslat Apr 07 '15 at 15:53
@Denslat, If you have to add collate clauses like this, your table design is poor. Same type of columns should always have same collation. – jarlh Apr 08 '15 at 06:45
@jarlh That's just the way it is, I can't change the database as it is the back end of a major program we use. – Denslat Apr 08 '15 at 11:08
@Denslat, too bad. Well, then you'll have to force a collation, just as in Andrew's answer. – jarlh Apr 08 '15 at 11:10

SQL Is it possible to collate twice?

1 Answers1