由于排序规则不匹配，对两个数据库的查询有错误 [英] Query over two databases has error due to collation mismatch

问题描述

我是一名IT顾问，通常负责管理硬件和网络，但是我们的程序员目前正在休假。我对在15个站点的实践管理软件服务器上的每个服务器上的两个数据库上运行的MSSQL查询进行了一些修改，并且在一些与归类冲突有关的服务器上遇到错误。

I'm an IT consultant that usually manages hardware and networks but our programmers are on leave at the moment. I've made some modifications to a MSSQL query that runs over two databases on each of our 15 site's Practice Management Software servers and I'm getting an error on a few of the servers relating to a collation conflict.

在我们大多数站点的服务器上，它运行良好，但是在15台服务器中，有4台由于某些原因出现了两台使用不同排序规则的数据库。

On most of our site's servers it runs fine, but there is 4 out the 15 servers that appear two have the databases using different collation for some reason.

我已经研究过尝试更改排序规则，但是操作并不顺利，所以我希望可以调整查询，以便排序规则的区别不会这些服务器无关紧要。实际上，如果查询是与排序规则无关的查询会更好，这样我就可以在所有服务器上使用相同的查询而不必担心排序规则了。

I've looked into trying to change the collation, but it didn't go well, so I was hoping maybe I could tweak the query so the difference in collation didn't matter for these servers. In fact, it'd be better if the query was 'collation agnostic' so that I could use the same query across all servers without a care for the collation... Can this be done?

确切的错误是

消息446，级别16，状态11，第1行
无法解析排序规则对于DISTINCT操作，在CASE运算符中， Latin1_General_CI_AS和 SQL_Latin1_General_CP1_CI_AS之间存在冲突。

Msg 446, Level 16, State 11, Line 1 Cannot resolve collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in CASE operator for DISTINCT operation.

消息446，级别16，状态11，第1行
无法解决排序规则冲突在CASE运算符中用于DISTINCT操作的 Latin1_General_CI_AS和 SQL_Latin1_General_CP1_CI_AS之间。

Msg 446, Level 16, State 11, Line 1 Cannot resolve collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in CASE operator for DISTINCT operation.

我正在运行的查询是：

SELECT DISTINCT
-- setup columns
[cases].[reference] as MatterNumber,
[dd_entity_d2].[type] as ClientType,
[dd_client].[clientname] as MatterName,
CASE WHEN [dd_entity_d2].[type] ='Individual' THEN etClient.FirstName ELSE [dd_entity_d4].[firstname] END AS FirstName,
CASE WHEN [dd_entity_d2].[type] ='Individual' THEN etClient.LastName   ELSE [dd_entity_d4].[lastname] END AS LastName,
CASE WHEN [dd_entity_d2].[type] ='Individual' THEN [dd_entity_d2].[email]   ELSE [dd_entity_d4].[email] END AS Email,
etActing.[PreferredName] ActingPerson,
[cases].[category] 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 = '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

PracticeEvolve_c1是 SQL_Latin1_General_CP1_CI_AS ，而PracticeEvolve_doc是 Latin1_General_CI_AS 。

PracticeEvolve_c1 is SQL_Latin1_General_CP1_CI_AS and PracticeEvolve_doc is Latin1_General_CI_AS.

我不在这里，不胜感激，您可能会提供任何帮助。

I'm out of my depth here and would greatly appreciate any help you may be able to offer.

干杯-Reece

EDIT： FWIW-这是我尝试更改数据库时遇到的错误整理：

FWIW - this is the error I got when I tried to change the database collation:

对象'MonthToDays365'取决于数据库整理。如果绑定模式的对象依赖数据库排序规则，则无法更改数据库排序规则。删除对数据库排序规则的依赖性，然后重试该操作

The object 'MonthToDays365' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation

推荐答案

只需修改CASE表达式并在 COLLATE 命令

Just amend the CASE expression and include the COLLATE command

CASE WHEN [dd_entity_d2].[type] COLLATE SQL_Latin1_General_CP1_CI_AS 
          ='Individual' 
THEN etClient.FirstName COLLATE SQL_Latin1_General_CP1_CI_AS 
ELSE [dd_entity_d4].[firstname] COLLATE SQL_Latin1_General_CP1_CI_AS 
END AS FirstName,

这篇关于由于排序规则不匹配，对两个数据库的查询有错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！