我有下面的代码
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的副本。
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 )
您的表中有两个不同的排序规则不匹配。你可以通过使用这个查询来检查你的表中每一列的排序规则:
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
Marc
更新:在你的数据库中找到全文索引，使用这个查询:
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)
多亏了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)
得到:
修改myTable关联列(4000)，将Latin1_General_CI_AS为空
我承认对col.max_length / 2 -的需求感到困惑
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 )
