无法解决“SQL_Latin1_General_CP1_CI_AS"之间的排序规则冲突和“Latin1_General_CI_AS"在等于操作中 [英] Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation
问题描述
我有以下代码
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 并已恢复此副本的事实有关db 到我 2005 年的机器上.
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.
推荐答案
您的表中有两种不同的排序规则不匹配.您可以使用以下查询检查表中每列的排序规则:
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
马克
更新:要在您的数据库中查找全文索引,请在此处使用此查询:
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)
这篇关于无法解决“SQL_Latin1_General_CP1_CI_AS"之间的排序规则冲突和“Latin1_General_CI_AS"在等于操作中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!