列与取消透视表列表中其他列的类型冲突

Question

我sys.[views]将重点放在关键值对上，以便与另一台服务器上的值进行比较以进行一致性测试。我遇到了一个返回错误的问题。

Msg 8167，第16级，状态1，第51行

列“类型”的类型与UNPIVOT列表中指定的其他列的类型冲突。

询问：

SELECT
sourceUnpivoted.idServer,
sourceUnpivoted.sourceServerName,
sourceUnpivoted.name,
sourceUnpivoted.columnName,
sourceUnpivoted.columnValue
FROM (
SELECT 
CAST('1' AS VARCHAR(255)) AS idServer,
CAST('thisOne' AS VARCHAR(255)) AS sourceServerName,
CAST('theDatabase' AS VARCHAR(255)) AS sourceDatabaseName,
CAST(name AS VARCHAR(255)) AS name,
CAST(object_id AS VARCHAR(255)) AS object_id,
CAST(principal_id AS VARCHAR(255)) AS principal_id,
CAST(schema_id AS VARCHAR(255)) AS schema_id,
CAST(parent_object_id AS VARCHAR(255)) AS parent_object_id,
CAST(type AS VARCHAR(255)) AS type,
CAST(type_desc AS VARCHAR(255)) AS type_desc,
CAST(create_date AS VARCHAR(255)) AS create_date,
CAST(lock_escalation_desc AS VARCHAR(255)) AS lock_escalation_desc
...
FROM noc_test.dbo.stage_sysTables
) AS databaseTables
UNPIVOT (
columnValue FOR columnName IN (
object_id,
principal_id,
schema_id,
parent_object_id,
type,
type_desc,
create_date,
lock_escalation_desc
) 
) AS sourceUnpivoted

为什么这样不喜欢[type]，[type_desc]，[lock_escalation_desc]???
我也尝试过CONVERT(VARCHAR(255),type) AS type

Answer 1

这实际上是整理问题。我可以通过更改以下行来解决：

CAST([type] collate database_default AS VARCHAR(255)) AS [type], 
CAST(type_desc collate database_default AS VARCHAR(255)) AS type_desc, 
CAST(create_date AS VARCHAR(255)) AS create_date, 
CAST(lock_escalation_desc collate database_default AS VARCHAR(255)) AS lock_escalation_desc

特定的问题是name整理为Latin1_General_CI_AS，而您提到的其他3列则整理为Latin1_General_CI_AS_KS_WS（至少，在我的机器上，我不确定在使用不同默认排序规则的服务器/数据库上的情况）。