无法解决“SQL_Latin1_General_CP1_CI_AS"和“;Latin1_General_CI_AS"在equal to运算中

我有下面的代码

SELECT tA.FieldName As [Field Name],
COALESCE(tO_A.[desc], tO_B.[desc], tO_C.Name, tA.OldVAlue) AS [Old Value],
COALESCE(tN_A.[desc], tN_B.[desc], tN_C.Name, tA.NewValue) AS [New Value],
U.UserName AS [User Name],
CONVERT(varchar, tA.ChangeDate) AS [Change Date]
FROM D tA
JOIN
[DRTS].[dbo].[User] U
ON tA.UserID = U.UserID
LEFT JOIN
A tO_A
on tA.FieldName = 'AID'
AND tA.oldValue = CONVERT(VARCHAR, tO_A.ID)
LEFT JOIN
A tN_A
on tA.FieldName = 'AID'
AND tA.newValue = CONVERT(VARCHAR, tN_A.ID)
LEFT JOIN
B tO_B
on tA.FieldName = 'BID'
AND tA.oldValue = CONVERT(VARCHAR, tO_B.ID)
LEFT JOIN
B tN_B
on tA.FieldName = 'BID'
AND tA.newValue = CONVERT(VARCHAR, tN_B.ID)
LEFT JOIN
C tO_C
on tA.FieldName = 'CID'
AND tA.oldValue = tO_C.Name
LEFT JOIN
C tN_C
on tA.FieldName = 'CID'
AND tA.newValue = tN_C.Name
WHERE U.Fullname = @SearchTerm
ORDER BY tA.ChangeDate

当运行代码时，我在添加表c的两个连接后，将错误粘贴在标题中。我认为这可能与我使用SQL Server 2008的事实有关，并在2005年的机器上恢复了这个db的副本。

1000725 次浏览
小开
最佳答案

您的表中有两个不同的排序规则不匹配。你可以通过使用这个查询来检查你的表中每一列的排序规则:

SELECT
col.name, col.collation_name
FROM
sys.columns col
WHERE
object_id = OBJECT_ID('YourTableName')

在排序和比较字符串时需要并使用排序规则。在整个数据库中使用单一的、唯一的排序规则通常是个好主意——不要在单个表或数据库中使用不同的排序规则——你只会自找麻烦....

一旦你确定了一个排序规则，你可以使用下面的命令改变那些不匹配的表/列:

ALTER TABLE YourTableName
ALTER COLUMN OffendingColumn
VARCHAR(100) COLLATE Latin1_General_CI_AS NOT NULL

马克

更新:在你的数据库中找到全文索引，使用这个查询:

SELECT
fti.object_Id,
OBJECT_NAME(fti.object_id) 'Fulltext index',
fti.is_enabled,
i.name 'Index name',
OBJECT_NAME(i.object_id) 'Table name'
FROM
sys.fulltext_indexes fti
INNER JOIN
sys.indexes i ON fti.unique_index_id = i.index_id

然后你可以删除全文索引使用:

DROP FULLTEXT INDEX ON (tablename)
小开

我以前有过类似的东西，我们发现两个表之间的排序是不同的。

检查它们是否相同。

小开

在查询中使用collate子句:

LEFT JOIN C tO_C on tA.FieldName = 'CID' AND tA.oldValue COLLATE Latin1_General_CI_AS = tO_C.Name

我可能没有完全正确的语法(检查BOL)，但是您可以这样做来动态地更改查询的排序规则—您可能需要为每个连接添加子句。

编辑:我意识到这是不完全正确的- collate子句在你需要改变的字段之后-在这个例子中，我改变了tA.oldValue字段的排序规则。

小开

我做以下事情:

...WHERE
fieldname COLLATE DATABASE_DEFAULT = otherfieldname COLLATE DATABASE_DEFAULT

每次都管用。：）

小开

根本原因是您从中获取模式的sql server数据库的排序规则与您的本地安装不同。如果您不想担心排序规则，请使用与SQL Server 2008数据库相同的排序规则在本地安装SQL Server。

小开

对于那些有一个CREATE DATABASE脚本(就像我的情况)的数据库，导致这个问题，你可以使用下面的CREATE脚本来匹配排序规则:

-- Create Case Sensitive Database
CREATE DATABASE CaseSensitiveDatabase
COLLATE SQL_Latin1_General_CP1_CS_AS -- or any collation you require
GO
USE CaseSensitiveDatabase
GO
SELECT *
FROM sys.types
GO
--rest of your script here

或

-- Create Case In-Sensitive Database
CREATE DATABASE CaseInSensitiveDatabase
COLLATE SQL_Latin1_General_CP1_CI_AS -- or any collation you require
GO
USE CaseInSensitiveDatabase
GO
SELECT *
FROM sys.types
GO
--rest of your script here
这将对所有表应用所需的排序规则，这正是我所需要的。理想的做法是，尽量保持服务器上所有数据库的排序规则相同。

有关以下链接的更多信息:SQL SERVER -在服务器上创建不同排序规则的数据库

小开

我有一个类似的错误(无法解决“SQL_Latin1_General_CP1_CI_AS”和“SQL_Latin1_General_CP1250_CI_AS”在INTERSECT操作之间的排序冲突)，当我使用旧的jdbc驱动程序。

我通过从微软或开源项目jTDS下载新的驱动程序来解决这个问题。

小开

下面是我们所做的，在我们的情况下，我们需要一个临时查询，使用日期限制按需执行，查询定义在一个表中。

我们的新查询需要匹配不同数据库之间的数据，并包括来自这两个数据库的数据。

从iSeries/AS400系统导入数据的db和我们的报告数据库之间的COLLATION似乎是不同的——这可能是因为特定的数据类型(例如名称上的希腊重音等等)。

所以我们使用了下面的join子句:

...LEFT Outer join ImportDB..C4CTP C4 on C4.C4CTP COLLATE Latin1_General_CS_AS=CUS_Type COLLATE Latin1_General_CS_AS
小开
确定抛出此错误的字段，并向它们添加以下内容: 核对DATABASE_DEFAULT < / p >

Code字段中有两个表连接:

...
and table1.Code = table2.Code
...

将查询更新为:

...
and table1.Code COLLATE DATABASE_DEFAULT = table2.Code COLLATE DATABASE_DEFAULT
...
小开

多亏了marc_s的回答，我解决了我最初的问题——受到启发，我想更进一步，发布一种一次转换整个表的方法——tsql脚本生成alter列语句:

DECLARE @tableName VARCHAR(MAX)
SET @tableName = 'affiliate'
--EXEC sp_columns @tableName
SELECT  'Alter table ' + @tableName + ' alter column ' + col.name
+ CASE ( col.user_type_id )
WHEN 231
THEN ' nvarchar(' + CAST(col.max_length / 2 AS VARCHAR) + ') '
END + 'collate Latin1_General_CI_AS ' + CASE ( col.is_nullable )
WHEN 0 THEN ' not null'
WHEN 1 THEN ' null'
END
FROM    sys.columns col
WHERE   object_id = OBJECT_ID(@tableName)
< p >得到: ALTER TABLE关联列myTable NVARCHAR(4000) COLLATE Latin1_General_CI_AS NOT NULL

我承认对Col.max_length / 2 -的需求感到困惑

小开

当您有2个不同的数据库，特别是来自2个不同服务器的2个不同数据库时，很容易发生这种情况。最好的选择是将其更改为公共集合并进行连接或比较。

SELECT
*
FROM sd
INNER JOIN pd ON sd.SCaseflowID COLLATE Latin1_General_CS_AS = pd.PDebt_code COLLATE Latin1_General_CS_AS
小开 
ALTER DATABASE test2            --put your database name here
COLLATE Latin1_General_CS_AS    --replace with the collation you need
小开

我已经使用网站的内容创建了以下脚本，它改变了所有表中所有列的排序规则:

CREATE PROCEDURE [dbo].[sz_pipeline001_collation]
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;




SELECT 'ALTER TABLE [' + SYSOBJECTS.Name + '] ALTER COLUMN [' + SYSCOLUMNS.Name + '] ' +
SYSTYPES.name +
CASE systypes.NAME
WHEN 'text' THEN ' '
ELSE
'(' + RTRIM(CASE SYSCOLUMNS.length
WHEN -1 THEN 'MAX'
ELSE CONVERT(CHAR,SYSCOLUMNS.length)
END) + ') '
END


+ ' ' + ' COLLATE Latin1_General_CI_AS ' + CASE ISNULLABLE WHEN 0 THEN 'NOT NULL' ELSE 'NULL' END
FROM SYSCOLUMNS , SYSOBJECTS , SYSTYPES
WHERE SYSCOLUMNS.ID = SYSOBJECTS.ID
AND SYSOBJECTS.TYPE = 'U'
AND SYSTYPES.Xtype = SYSCOLUMNS.xtype
AND SYSCOLUMNS.COLLATION IS NOT NULL
AND NOT ( sysobjects.NAME LIKE 'sys%' )
AND NOT ( SYSTYPES.name LIKE 'sys%' )


END
小开

你可以通过4个简单的步骤轻松做到这一点

  1. 备份你的数据库，以防万一
  2. 更改数据库排序规则:右键单击数据库，选择属性，转到选项并将排序规则更改为所需的排序规则。
  3. 生成一个脚本来删除和重新创建所有数据库对象:右键单击数据库，选择任务，选择生成脚本…(确保你选择了Drop &在向导的高级选项中创建，同时选择模式&数据)
  4. 运行上面生成的脚本
小开

@瓦尔基里的回答真棒。我想我在这里放了一个在存储过程中执行相同的子查询的情况，因为我想知道你的答案在这种情况下是否有效，它做得很棒。

...WHERE fieldname COLLATE DATABASE_DEFAULT in (
SELECT DISTINCT otherfieldname COLLATE DATABASE_DEFAULT
FROM ...
WHERE ...
)
小开

检查不匹配的排序规则级别(服务器、数据库、表、列、字符)。

如果是服务器，这些步骤曾经帮助过我:

  1. 停止服务器
  2. 找到sqlservr.exe工具

  3. 执行命令:

    sqlservr -m -T4022 -T3659 -s" name_of_instance " q“name_of_collation”< /代码> < / p > < /李>

  4. 启动sql server

    net start name_of_instance < / p >

  5. 再次检查服务器的排序规则。

以下是更多信息:

https://www.mssqltips.com/sqlservertip/3519/changing-sql-server-collation-after-installation/

小开

错误(无法解决....之间的排序冲突)通常在比较来自多个数据库的数据时发生。

因为你现在不能改变数据库的排序规则，所以使用COLLATE DATABASE_DEFAULT。

----------
AND db1.tbl1.fiel1 COLLATE DATABASE_DEFAULT =db2.tbl2.field2 COLLATE DATABASE_DEFAULT
小开

如果这种情况发生在整个数据库中，那么最好像这样改变你的数据库排序规则:

USE master;
GO
ALTER DATABASE MyOptionsTest
COLLATE << INSERT COLATION REQUIRED >> ;
GO


--Verify the collation setting.
SELECT name, collation_name
FROM sys.databases
WHERE name = N'<< INSERT DATABASE NAME >>';
GO

参考在这里

小开 
INSERT INTO eSSLSmartOfficeSource2.[dbo].DeviceLogs  (DeviceId,UserId,LogDate,UpdateFlag)
SELECT DL1.DeviceId ,DL1.UserId COLLATE DATABASE_DEFAULT,DL1.LogDate
,0 FROM eSSLSmartOffice.[dbo].DeviceLogs DL1
WHERE  NOT EXISTS
(SELECT DL2.DeviceId ,DL2.UserId COLLATE DATABASE_DEFAULT
,DL2.LogDate ,DL2.UpdateFlag
FROM eSSLSmartOfficeSource2.[dbo].DeviceLogs DL2
WHERE  DL1.DeviceId =DL2.DeviceId
and DL1.UserId collate  Latin1_General_CS_AS=DL2.UserId collate  Latin1_General_CS_AS
and DL1.LogDate =DL2.LogDate )
小开

在where条件中添加collate SQL_Latin1_General_CP1_CI_AS

这对我很有用。

WHERE U.Fullname = @SearchTerm  collate SQL_Latin1_General_CP1_CI_AS
小开

添加代码@JustSteve的答案，以处理varchar和varchar(MAX)列:

DECLARE @tableName VARCHAR(MAX)
SET @tableName = 'first_notes'
--EXEC sp_columns @tableName
SELECT  'Alter table ' + @tableName + ' alter column ' + col.name
+ CASE ( col.user_type_id )
WHEN 231
THEN ' nvarchar(' + CAST(col.max_length / 2 AS VARCHAR) + ') '
WHEN 167
THEN ' varchar(' + CASE col.max_length
WHEN -1
THEN 'MAX'
ELSE
CAST(col.max_length AS VARCHAR)
end
+ ') '
END + 'collate Latin1_General_CI_AS ' + CASE ( col.is_nullable )
WHEN 0 THEN ' not null'
WHEN 1 THEN ' null'
END
FROM    sys.columns col
WHERE   object_id = OBJECT_ID(@tableName)
小开

您的数据库中可能没有任何排序规则问题，但是如果您从服务器上的备份恢复了数据库的副本，其排序规则与原始数据库不同，并且您的代码正在创建临时表，那么这些临时表将从服务器继承排序规则，并将与您的数据库发生冲突。

小开

要在不改变任何数据库的情况下解决查询中的这个问题，您可以强制转换“=”符号另一侧的表达式

COLLATE SQL_Latin1_General_CP1_CI_AS

在这里。

小开

我也有类似的要求;在这里为任何有类似情况的人记录我的方法……

场景

  • 我有一个数据库从一个干净的安装与正确的排序规则。
  • 我有另一个数据库，它有错误的排序。
  • 我需要更新后者以使用在前者上定义的排序规则。

解决方案

使用SQL Server模式比较(from SQL Server数据工具 / Visual Studio)比较源(干净安装)和目标(无效排序规则的db)。

在我的例子中，我直接比较了两个db;虽然你可以通过一个项目，让你手动调整之间的部分…

  • 运行Visual Studio
  • 创建一个新的SQL Server数据项目
  • 单击“工具”、“SQL Server”、“新模式比较”
  • 选择源数据库
  • 选择目标数据库
  • 点击选项()
    • 在Object Types下只选择那些你感兴趣的类型(对我来说只有Views和Tables)
      • 在General下选择:
      • 阻止可能的数据丢失
      • 禁用,重新启用DDL触发器
      • 忽略加密提供程序文件路径
      • 忽略文件&日志路径
      • 忽略文件大小
      • 忽略文件组位置
      • 忽略全文目录文件路径
      • 忽略关键字套管
      • 忽略登录sid
      • 忽略引号标识符
      • 忽略路由存活时间
      • 忽略语句之间的分号
      • 忽略空格
      • 脚本刷新模块
      • 新约束的脚本验证
      • 验证排序规则的兼容性
      • 验证部署
        • 李< / ul > < / > 李< / ul > < / > <李>点击比较
        • 取消选中任何标记为删除的对象(注意:这些对象可能仍然有排序问题;但是因为它们没有在我们的source/template db中定义，所以我们不知道;无论哪种方式，如果我们只针对排序规则的更改，我们都不希望丢失东西)。您可以通过右键单击DELETE文件夹并选择EXCLUDE来一次性取消选中。
        • 同样排除任何CREATE对象(这里因为它们不存在于目标中，所以它们在那里不能有错误的排序规则;它们是否应该存在是另一个话题了)。
        • 单击CHANGE下的每个对象以查看该对象的脚本。使用diff来确保我们只改变排序规则(任何手动检测到的其他差异，您可能希望手动排除/处理这些对象)。
          • 李< / ul > < / >
        • 单击Update来推送更改

        这仍然需要一些手工工作(例如，检查你只影响排序)-但它为你处理依赖关系。

        此外，您还可以保留一个有效模式的数据库项目，以便在有多个数据库需要更新时为您的数据库使用通用模板，假设所有目标数据库最终都应该具有相同的模式。

        你也可以对数据库项目中的文件使用find/replace，如果你希望大量修改那里的设置(例如，你可以使用模式比较从无效的数据库创建项目，修改项目文件，然后在模式比较中切换源/目标，将你的更改推回DB)。

小开

到目前为止，我几乎阅读了这里的每一个答案和评论。它让我得到了一个简单的解决方案，通过结合所做的反应。下面是我解决问题的简单方法:

  1. 创建数据库脚本。右键单击数据库>在的任务;生成脚本。一定要包括模式和数据
  2. 保存脚本后删除数据库。右键单击数据库>删除
  3. 删除脚本中重建数据库的部分，即删除第一行之前的所有内容:

使用& lt;数据库名比;

去

  1. 手动创建数据库，即右键单击Tables >创建数据库……

  2. 运行为新的空数据库设置默认排序规则的脚本。

    USE master 去
    ALTER DATABASE <<数据库名祝辞祝辞< / p > < p >整理& lt; & lt;INSERT COLATION REQUIRED >>,
    去< / p >

  3. 运行您保存的脚本重新创建数据库

信贷

  1. @Justin提供了检查数据库排序的脚本，以及如何更新它
  2. @RockScience提到排序规则的改变只适用于新的表/对象
  3. @Felix Mwiti Mugambi(感谢我的肯尼亚同胞:))指出需要重建数据库。(我通常避免删除和创建复杂的数据库)

提交答案