SQL Server: Cannot resolve the collation conflict, despite conversion

Question

I still get the error code

Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

despite the fact that I'm using conversion as per these two answers. Here is my code:

-- ...
from #TmpResult a
left join #GroupMemberTable b
on (a.DBO_Owner_Login COLLATE SQL_Latin1_General_CP1_CI_AS) = (b.login_name COLLATE SQL_Latin1_General_CP1_CI_AS)

I have converted them both to: SQL_Latin1_General_CP1_CI_AS

Thank you

UPDATE: Here is the full excerpt:

create table #TmpTableSec3 (database_name varchar(100), Database_Owner varchar(200), DBO_Owner_Login varchar(200), type varchar (100), privilege varchar(100), group_name varchar(500))
insert into #TmpTableSec3
select a.database_name, a.principal, a.DBO_Owner_Login, b.type, b.privilege, b.group_name
from #TmpResult a
left join #GroupMemberTable b
on (a.DBO_Owner_Login COLLATE SQL_Latin1_General_CP1_CI_AS) = (b.login_name COLLATE SQL_Latin1_General_CP1_CI_AS)
    --a.DBO_Owner_Login = b.login_name
    --fieldname COLLATE DATABASE_DEFAULT = otherfieldname COLLATE DATABASE_DEFAULT
where b.group_name is not null --and b.privilege = 'admin'
order by a.database_name

Then that table is populated by:

insert into #TmpResult
select a.database_name, a.Owner, b.DBO_Owner_Login from #TmpTableSec1 a
join #TmpTableSec2 b
on a.database_name = b.database_name


  set @cmd = 'select name,  suser_sname(owner_sid) from master.sys.databases where name = '''+@name+''''
  --select @cmd
  insert #TmpTableSec1 exec (@cmd)
  --select @dbowner = (select suser_sname(owner_sid) from master.sys.databases where name = @name)
  set @cmd = 'use '+ @name +'
  select db_name(), name, suser_sname(sid)
  from sys.database_principals where name = ''dbo'''
  --select ''@DBO'' = (select suser_sname(sid) from sys.database_principals where name = ''dbo'') '
  INSERT #TmpTableSec2 exec (@cmd)

@NickyvV I posted more, but I think the issue is only related to that one line — LearnByReading
You are inserting data to a table, the error is most likely that the collation definition for some column is different than the one you are inserting. If the problem was with a.DBO_Owner_Login, you might want to use a.DBO_Owner_Login COLLATE SQL_Latin1_General_CP1_CI_AS in the SELECT too (or the appropriate collation) — Lamak
By chance is one of the fields stored as text in the original tables? — HLGEM

McNets McNets · Accepted Answer · 2016-11-08T23:12:41

drop table #test
drop table #test2

create table #test
(id int, name varchar(100) collate Latin1_General_CI_AI
CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED ([id] ASC)
)

create table #test2
(id int, name varchar(100) collate SQL_Latin1_General_CP1_CI_AS
CONSTRAINT [PK_test2] PRIMARY KEY CLUSTERED ([id] ASC)
)

insert into #test values (1, 'premonition')
insert into #test values (2, 'premònition')
insert into #test values (3, 'lowmotion')

insert into #test2 values (1, 'premonition')
insert into #test2 values (2, 'premònition')
insert into #test2 values (3, 'lowmotion')

select * 
from #test 
inner join #test2 
    on #test.name = #test2.name collate SQL_Latin1_General_CP1_CI_AS

collate SQL_Latin1_General_CP1_CI_AS forces the comparison to this collate, I'm using the same collation defined in #test2.name

Maybe this 'Collation precedence' article can help you.

https://msdn.microsoft.com/en-us/library/ms179886.aspx

SQL Server: Cannot resolve the collation conflict, despite conversion

1 Answers