嘿，我来帮你搞定这个跨数据库查询的排序规则冲突问题——不用费劲改数据库排序规则（毕竟那个依赖对象MonthToDays365拦着呢），直接调整查询语句就能让它在所有15台服务器上通用。

问题到底出在哪？ #

你的查询里，CASE操作符用到的字符串字段分别来自两个排序规则不一样的数据库：

• PracticeEvolve_c1：SQL_Latin1_General_CP1_CI_AS
• PracticeEvolve_doc：Latin1_General_CI_AS

当DISTINCT需要去重、ORDER BY需要排序时，SQL Server找不到一个统一的排序规则来对比这些字符串，就抛出了Msg 446的冲突错误。

最简单的解决方案：显式统一排序规则 #

我们不用动数据库的设置，只需要在查询里给每个跨库的字符串字段显式指定同一个排序规则就行。你可以任选其中一个数据库的规则作为统一标准，这里我选SQL_Latin1_General_CP1_CI_AS（换成Latin1_General_CI_AS也没问题，只要全程保持一致）。

修改后的完整查询语句 #

SELECT DISTINCT 
-- setup columns
[cases].[reference] as MatterNumber,
[dd_entity_d2].[type] COLLATE SQL_Latin1_General_CP1_CI_AS as ClientType,
[dd_client].[clientname] COLLATE SQL_Latin1_General_CP1_CI_AS as MatterName,
CASE WHEN [dd_entity_d2].[type] COLLATE SQL_Latin1_General_CP1_CI_AS ='Individual' THEN etClient.FirstName ELSE [dd_entity_d4].[firstname] COLLATE SQL_Latin1_General_CP1_CI_AS END AS FirstName,
CASE WHEN [dd_entity_d2].[type] COLLATE SQL_Latin1_General_CP1_CI_AS ='Individual' THEN etClient.LastName ELSE [dd_entity_d4].[lastname] COLLATE SQL_Latin1_General_CP1_CI_AS END AS LastName,
CASE WHEN [dd_entity_d2].[type] COLLATE SQL_Latin1_General_CP1_CI_AS ='Individual' THEN [dd_entity_d2].[email] COLLATE SQL_Latin1_General_CP1_CI_AS ELSE [dd_entity_d4].[email] COLLATE SQL_Latin1_General_CP1_CI_AS END AS Email,
etActing.[PreferredName] COLLATE SQL_Latin1_General_CP1_CI_AS ActingPerson,
[cases].[category] COLLATE SQL_Latin1_General_CP1_CI_AS as MatterType,
mt.CreatedOn as MatterOpened,
case mt.[Status] when 0 then 'In Progress' When 1 then 'On Hold' when 2 then 'Completed' when 3 then 'Not Proceeding' else 'Unknown' end as MatterStatus
-- mt.LastUpdatedOn as LastModified,
-- end columns
-- setup data
FROM PracticeEvolve_doc.dbo.[cases]
INNER JOIN PracticeEvolve_c1.dbo.DocumaticsMap dm on dm.DocumaticsID = [cases].ID and dm.Entitytype COLLATE SQL_Latin1_General_CP1_CI_AS = 'Matter'
INNER JOIN PracticeEvolve_c1.dbo.[Matter] mt on mt.Matterid = dm.ClickOneID
INNER JOIN PracticeEvolve_c1.dbo.[Client] cl on mt.ClientID = cl.ClientID
INNER JOIN PracticeEvolve_c1.dbo.[Entity] etClient on cl.EntityID = etClient.EntityID
LEFT JOIN PracticeEvolve_c1.dbo.EmployeeMatter emActing on emActing.MatterID = mt.MatterID and emActing.AssociationTypeID = 15
LEFT JOIN PracticeEvolve_c1.dbo.Employee eActing on eActing.EmployeeID = emActing.EmployeeID
LEFT JOIN PracticeEvolve_c1.dbo.Entity etActing on etActing.EntityID = eActing.EntityID
LEFT JOIN PracticeEvolve_doc.dbo.[dd_client] ON [dd_client].[id]=[cases].[clientid]
LEFT JOIN PracticeEvolve_doc.dbo.[dd_manytomany] AS [dd_manytomanydd_entity_d2] ON [dd_manytomanydd_entity_d2].[fkid] = [dd_client].[fk_entities]
LEFT JOIN PracticeEvolve_doc.dbo.[dd_entity] as [dd_entity_d2] ON [dd_entity_d2].[id] = [dd_manytomanydd_entity_d2].[pkid]
LEFT JOIN PracticeEvolve_doc.dbo.[dd_manytomany] AS [dd_manytomanydd_party_d3] ON [dd_manytomanydd_party_d3].[fkid] = [dd_entity_d2].[fk_parties]
LEFT JOIN PracticeEvolve_doc.dbo.[dd_party] as [dd_party_d3] ON [dd_party_d3].[id] = [dd_manytomanydd_party_d3].[pkid]
LEFT JOIN PracticeEvolve_doc.dbo.[dd_manytomany] AS [dd_manytomanydd_entity_d4] ON [dd_manytomanydd_entity_d4].[fkid] = [dd_party_d3].[fk_entity]
LEFT JOIN PracticeEvolve_doc.dbo.[dd_entity] as [dd_entity_d4] ON [dd_entity_d4].[id] = [dd_manytomanydd_entity_d4].[pkid]
-- end data
-- setup filters
WHERE [cases].[deleted]=0
-- AND DATEPART(m, mt.CreatedOn) = DATEPART(m, DATEADD(m, -1, getdate()))
-- AND DATEPART(yyyy, mt.CreatedOn) = DATEPART(yyyy, DATEADD(m, -1, getdate()))
AND mt.CreatedOn >= '2015-07-01'
-- AND [dd_entity_d2].[type] = 'Individual'
-- AND mt.LastUpdatedOn >= '2017-04-02'
-- AND mt.[status] = 0
-- end filters
-- setup sort
ORDER BY Email ASC
-- end sort and query

我改了哪些地方？ #

1. 所有来自PracticeEvolve_doc的字符串字段：比如[dd_entity_d2].[type]、[dd_client].[clientname]、[dd_entity_d4].[firstname]等，都加上了COLLATE SQL_Latin1_General_CP1_CI_AS，确保和PracticeEvolve_c1的字段排序规则统一。
2. JOIN条件里的字符串对比：比如dm.Entitytype = 'Matter'，给dm.Entitytype也加了排序规则，避免潜在的冲突。
3. SELECT里的其他字符串字段：比如MatterType、ActingPerson，也统一了排序规则，防止后续操作（比如DISTINCT）触发新的冲突。

这样改完之后，你的查询就完全“免疫”排序规则差异了，不管哪台服务器上两个数据库的规则是不是一样，都能正常跑起来。

内容的提问来源于stack exchange，提问作者Reece