我有下面的代码
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的副本。
您的表中有两个不同的排序规则不匹配。你可以通过使用这个查询来检查你的表中每一列的排序规则:
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)
对于那些有一个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
这将对所有表应用所需的排序规则，这正是我所需要的。理想的做法是，尽量保持服务器上所有数据库的排序规则相同。
希望这能有所帮助。
有关以下链接的更多信息:SQL SERVER -在服务器上创建具有不同排序规则的数据库
多亏了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 )
我也有类似的要求;在这里为任何有类似情况的人记录我的方法……
场景
我有一个数据库从一个干净的安装与正确的排序规则。
我有另一个数据库，它有错误的排序。
我需要更新后者以使用在前者上定义的排序规则。
解决方案
使用SQL Server模式比较(来自SQL Server数据工具/ Visual Studio)来比较源(干净安装)和目标(无效排序规则的db)。
在我的例子中，我直接比较了两个db;虽然你可以通过一个项目，让你手动调整之间的部分…
Run Visual Studio
Create a new SQL Server Data Project
Click Tools, SQL Server, New Schema Comparison
Select the source database
Select the target database
Click options (⚙)
Under Object Types select only those types you're interested in (for me it was only Views and Tables)
Under General select:
Block on possible data loss
Disable & reenable DDL triggers
Ignore cryptographic provider file path
Ignore File & Log File Path
Ignore file size
Ignore filegroup placement
Ignore full text catalog file path
Ignore keyword casing
Ignore login SIDs
Ignore quoted identifiers
Ignore route lifetime
Ignore semicolon between statements
Ignore whitespace
Script refresh module
Script validation for new constraints
Verify collation compatibility
Verify deployment
Click Compare
Uncheck any objects flagged for deletion (NB: those may still have collation issues; but since they're not defined in our source/template db we don't know; either way, we don't want to lose things if we're only targeting collation changes). You can unchceck all at once by right clicking on the DELETE folder and selecting EXCLUDE.
Likewise exclude for any CREATE objects (here since they don't exist in the target they can't have the wrong collation there; whether they should exist is a question for another topic).
Click on each object under CHANGE to see the script for that object. Use the diff to ensure that we're only changing the collation (anything other differences manually detected you'll likely want to exclude / handle those objects manually).
Click Update to push changes
这仍然需要一些手工工作(例如，检查你只影响排序)-但它为你处理依赖关系。
此外，您还可以保留一个有效模式的数据库项目，以便在有多个数据库需要更新时为您的数据库使用通用模板，假设所有目标数据库最终都应该具有相同的模式。
你也可以对数据库项目中的文件使用find/replace，如果你希望大量修改那里的设置(例如，你可以使用模式比较从无效的数据库创建项目，修改项目文件，然后在模式比较中切换源/目标，将你的更改推回DB)。
