列与逆透视列表中其他列的类型冲突 [英] Column conflicts with the type of other columns in the unpivot list
问题描述
我将 sys.[views]
转换为键值对,以便与另一台服务器上的值进行比较以进行一致性测试.我遇到了一个返回错误的问题.
Im pivoting sys.[views]
into key value pairs to compare with values on another server for consistency testing. Im running into an issue which returns the error.
消息 8167,级别 16,状态 1,第 51 行
Msg 8167, Level 16, State 1, Line 51
列的类型type"与 UNPIVOT 列表中指定的其他列的类型冲突.
The type of column "type" conflicts with the type of other columns specified in the UNPIVOT list.
查询:
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
Why does this not like [type]
,[type_desc]
,[lock_escalation_desc]
???
Ive also tried CONVERT(VARCHAR(255),type) AS type
推荐答案
这实际上是一个整理问题.我可以通过更改这些行来解决它:
It's actually a collation issue. I can resolve it by changing these lines:
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
(至少,在我的机器上,我不确定在具有不同默认排序规则的服务器/数据库上会是什么样子).
The specific issue is that name
is collated as Latin1_General_CI_AS
, whereas the other 3 columns you mentioned are collated as Latin1_General_CI_AS_KS_WS
(At least, on my machine, I'm not sure what it would be like on a server/database with different default collation).
这篇关于列与逆透视列表中其他列的类型冲突的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!