April 13, 2010 at 11:57 am
hello all...I am running into an issue when i am trying to join 2 tables from 2 different servers and 2 different databases. I am doing a left join and also tried a subselect and get the same error. I dont know what to do to fix this. I am hoping this will be something simple i can do but i have a feeling that its not going to be. here is the error i get when running my code.
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_BIN" in the equal to operation.
I linked the 2 servers to each other and still nothing. PLEASE HELP!!!! Thanks
April 13, 2010 at 12:21 pm
Please post your code, it will help. Also, which tables have which collation?
April 13, 2010 at 12:24 pm
select distinct pat.vst_ext_id, pat.adm_ts
from DTR.paragon_rpt.dbo.TPM300_PAT_VISIT pat
/*
left join HPFDATA.his.dbo.ENCOUNTERS ENC
on pat.vst_ext_id = ENC.ENCOUNTER
*/
where pat.vst_sta_cd = '14795' and pat.adm_ts >= '01/01/2010' and pat.adm_ts <= '01/13/2010'
and not exists (select distinct ENC.ENCOUNTER
from HPFDATA.his.dbo.ENCOUNTERS ENC
where pat.vst_ext_id = ENC.ENCOUNTER )
--order by pat.adm_ts
April 13, 2010 at 12:31 pm
Try this:
select distinct
pat.vst_ext_id,
pat.adm_ts
from
DTR.paragon_rpt.dbo.TPM300_PAT_VISIT pat
/*
left join HPFDATA.his.dbo.ENCOUNTERS ENC
on pat.vst_ext_id = ENC.ENCOUNTER
*/
where
pat.vst_sta_cd = '14795' and
pat.adm_ts >= '01/01/2010' and
pat.adm_ts <= '01/13/2010'
and not exists ( select distinct
ENC.ENCOUNTER
from
HPFDATA.his.dbo.ENCOUNTERS ENC
where
pat.vst_ext_id collate SQL_Latin1_General_CP1_CI_AS = ENC.ENCOUNTER collate SQL_Latin1_General_CP1_CI_AS)
--order by pat.adm_ts
April 13, 2010 at 12:42 pm
the DTR.paragon_rpt.dbo.TPM300_PAT_VISIT is the Latin1_General_BIN
and the HPFDATA.his.dbo.ENCOUNTERS is the SQL_Latin1_General_CP1_CI_AS
does that make a difference in my code?
i forgot to include this in my last reply... sorry
April 13, 2010 at 1:50 pm
Lynn
thanks so much you are a life saver. that worked perfectly...thanks again. 😀
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply