SQL服务器:无法解析排序规则冲突,尽管转换
问题描述:
我仍然得到错误代码SQL服务器:无法解析排序规则冲突,尽管转换
无法解析“Latin1_General_CI_AS” 并在等于操作规则“SQL_Latin1_General_CP1_CI_AS”之间的排序规则冲突。
尽管事实上我使用的转换按照these两个answers。这里是我的代码:
-- ...
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)
我让他们都转换为:SQL_Latin1_General_CP1_CI_AS
谢谢
UPDATE: 以下是完整的摘录:
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
然后该表填充为:
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 = '''[email protected]+''''
--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)
答
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
强制与此相比整理,我用了#test2.name
定义相同的排序规则也许这个“整理优先”的文章可以帮助你。
问题可能是在''.....你的代码的一部分 – Lamak
请张贴您尝试执行 – NickyvV
@NickyvV我贴得更整个代码,但我认为这个问题是只与该行相关 – LearnByReading