2. 技术
    3. 在等于操作中无法解决“SQL

    在等于操作中无法解决“SQL

    技术2022-07-13  64

    本文翻译自：Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_CI_AS” in the equal to operation

    I have the following code 我有以下代码

    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

    When running the code I am getting the error pasted in the title after adding the two joins for table C. I think this may have something to do with the fact I'm using SQL Server 2008 and have restored a copy of this db on to my machine which is 2005. 在运行代码时，我在为表C添加两个连接后得到了标题中粘贴的错误。我认为这可能与我使用SQL Server 2008的事实有关，并已将此数据库的副本还原到我的机器是2005年。

    #1楼

    参考：https://stackoom.com/question/6kCO/在等于操作中无法解决-SQL-Latin-General-CP-CI-AS-和-Latin-General-CI-AS-之间的排序规则冲突

    #2楼

    For those who have a CREATE DATABASE script (as was my case) for the database that is causing this issue you can use the following CREATE script to match the collation: 对于那些对导致此问题的数据库具有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

    or 要么

    -- 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

    This applies the desired collation to all the tables, which was just what I needed. 这将所需的排序规则应用于所有表格，这正是我所需要的。 It is ideal to try and keep the collation the same for all databases on a server. 对于服务器上的所有数据库，尝试保持校对相同是理想的。 Hope this helps. 希望这可以帮助。

    More info on the following link: SQL SERVER – Creating Database with Different Collation on Server 有关以下链接的更多信息： SQL SERVER - 在服务器上创建具有不同排序规则的数据库

    #3楼

    You have a mismatch of two different collations in your table. 您的表中有两个不同的排序规则不匹配。 You can check what collations each column in your table(s) has by using this query: 您可以使用以下查询检查表中每列的排序规则：

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

    Collations are needed and used when ordering and comparing strings. 在排序和比较字符串时需要并使用排序规则。 It's generally a good idea to have a single, unique collation used throughout your database - don't use different collations within a single table or database - you're only asking for trouble.... 在整个数据库中使用单一，唯一的排序规则通常是一个好主意 - 不要在单个表或数据库中使用不同的排序规则 - 您只是在寻找麻烦....

    Once you've settled for one single collation, you can change those tables / columns that don't match yet using this command: 一旦您完成了单个排序规则，您就可以使用此命令更改那些不匹配的表/列：

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

    Marc 渣

    UPDATE: to find the fulltext indices in your database, use this query here: 更新：要在数据库中查找全文索引，请在此处使用此查询：

    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

    You can then drop the fulltext index using: 然后，您可以使用以下命令删除全文索引：

    DROP FULLTEXT INDEX ON (tablename)

    #4楼

    I have had something like this before, and what we found was that the collation between 2 tables were different. 我之前有类似的东西，我们发现两张桌子之间的整理是不同的。

    Check that these are the same. 检查这些是否相同。

    #5楼

    Use the collate clause in your query: 在查询中使用collate子句：

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

    I may not have the syntax exactly right (check BOL), but you can do this to change the collation on-the-fly for the query - you may need to add the clause for each join. 我可能没有完全正确的语法（检查BOL），但是你可以这样做来为查询动态更改排序规则 - 你可能需要为每个连接添加子句。

    edit: I realized this was not quite right - the collate clause goes after the field you need to change - in this example I changed the collation on the tA.oldValue field. 编辑：我意识到这不太对 - collat​​e子句在你需要更改的字段之后 - 在这个例子中我更改了tA.oldValue字段的排序tA.oldValue 。

    #6楼

    I do the following: 我做以下事情：

    ...WHERE fieldname COLLATE DATABASE_DEFAULT = otherfieldname COLLATE DATABASE_DEFAULT

    Works every time. 每次都有效。 :) :)

    Processed: 0.011, SQL: 10