无法解决“ SQL_Latin1_General_CP1_CI_AS”和“ Latin1_General_CI_AI”在等于操作时的排序规则冲突

我得到下面说的排序错误，因为我已经从一台台式机转移到笔记本电脑。我的数据库有数百个存储过程，因此任何解决方案，比如重写某些查询或修复某个列的排序规则，对我来说都是不可能的。

”无法解决“ SQL _ Latin1 _ General _ CP1 _ CI _ AS”和“ Latin1 _ General _ CI _ AI”在等于操作时的排序规则冲突

我的问题不是唯一的，我已经搜索了很多，但可用的解决方案建议我用一些不可行的代码覆盖查询。请提出一些解决方案，可以避免这种排序的事情。

我已经尝试过这样改变我的数据库排序规则。

ALTER DATABASE testDB
COLLATE French_CI_AI ;
GO

谢谢。

197391 次浏览
小开
最佳答案

Just use the following syntax to collate on the fly when joining tables with different collations. I integrate systems, so I have to do this a lot.

select * from [Product] p join [category] c
on
c.[Name] collate SQL_Latin1_General_CP1_CI_AS
=
p.[Name] collate SQL_Latin1_General_CP1_CI_AS
小开 
USE master;
GO
ALTER DATABASE PRATAP
COLLATE Latin1_General_CI_AS_KS_WS ;
GO


--Verify the collation setting.
SELECT name, collation_name
FROM sys.databases
WHERE name = N' PRATAP ';
GO
小开

jUST ADD this line -> SQL_Latin1_General_CP1_CI_AS in your joinings. Thats it :) https://premkt.blogspot.my/2016/12/error-cannot-resolve-collation-conflict.html

小开

This is because of the server collation setup. We need to change the server collation setup.

.\Setup.exe /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=DESKTOP-QUAN3NS\admin /SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS-- collation you want

Once you can run this command on C:\Program Files\Microsoft SQL Server\150\Setup Bootstrap\SQL2019 using Powershell.


提交答案