CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-collation
hemu_k
hemu_k

Reputation: 1

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "SQL_Latin1_General_CP850_BIN2"

I am using data in my query from another database from same server

I am getting the given error

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "SQL_Latin1_General_CP850_BIN2" in the equal to operation.. Native error: 468. SQLSTATE: 42000. Severity 16. MsgState 9. Line 39.

How do I fix it?

with
 main AS (
SELECT a.LBrCode,a.PrdAcctId,b.SexCode,a.DateOpen,
case when (a.AcctStat=3 and a.DateClosed>'30-jun-2021') or a.AcctStat<>3 then 'live' else 'cl' end r1
FROM ac_mast a INNER JOIN prd_mat b ON a.CustNo=b.CustNo
WHERE rtrim(substring(a.PrdAcctId,1,8)) IN ('abc') AND a.AcctType=32

)
,
card_data AS (
SELECT substring(b.primary_a_c_number,1,3) LBrCode,
substring(b.primary_a_c_number,4,4)+replicate(' ',4)+replicate('0',8)+substring(b.primary_a_c_number,8,8)+replicate('0',8) PrdAcctId,
card_number,card_process_date,first_tran_date
FROM [hk_RDS2.0].dbo.cardx1 b

select a.LBrCode,a.PrdacctId,b.Card_number from main a inner join card_data b
on a.LBrCode=b.LBrCode and a.PrdAcctId=b.PrdAcctId

please help

Upvotes: -1

Views: 4068

Answers (1)

allmhuran
allmhuran

Reputation: 4474

Just to take this one off the board...

Change the last part of your query to this:

select      a.LBrCode, 
            a.PrdacctId, 
            b.Card_number 
from        main      a 
inner join  card_data b on a.LBrCode = b.LBrCode collate database_default
                           and a.PrdAcctId = b.PrdAcctId

Upvotes: 1

Related Questions