SQLServer2008，xml列转换成varchar(8000)或varchar(max)，报错

[samyp1234]

执行如下SQL
select t1.session_id session_id , cast( t1.sql_text as varchar(8000)) SQL语句 ,
t1.elapsed_time 毫秒 , inserted_datetime 统计时刻,
row_number() over(partition by cast( t1.sql_text as varchar(8000) )
order by t1.elapsed_time) 排名
from t1
where inserted_datetime >= @before_day_start
and inserted_datetime <= @before_day_end

有的机器上，不报错；有的机器上，报错；报错如下
无法将一个或多个字符从 XML 转换为目标排序规则

怎么处理呢？
简单来说，就是，怎样安全的把xml类型的列，截取出来 前8000个字符 ？

[Dear SQL(燊)]

获得数据库排序规则，如下图数据库属性页 将转换的字段设为对应的排序规则

SELECT t1.session_id session_id,
       CAST(t1.sql_text COLLATE Chinese_PRC_CI_AS AS VARCHAR(8000)) SQL语句,
       t1.elapsed_time 毫秒,
       inserted_datetime 统计时刻,
       ROW_NUMBER() OVER(
           PARTITION BY CAST(t1.sql_text  COLLATE Chinese_PRC_CI_AS  AS VARCHAR(8000)) 
           ORDER BY t1.elapsed_time
       ) 排名
FROM   t1
WHERE  inserted_datetime >= @before_day_start
       AND inserted_datetime <= @before_day_end 

[samyp1234]

引用 2 楼 chenshanliang 的回复:

获得数据库排序规则，如下图数据库属性页


将转换的字段设为对应的排序规则

SELECT t1.session_id session_id,
       CAST(t1.sql_text COLLATE Chinese_PRC_CI_AS AS VARCHAR(8000)) SQL语句,
       t1.elapsed_time 毫秒,
       inserted_datetime 统计时刻,
       ROW_NUMBER() OVER(
           PARTITION BY CAST(t1.sql_text  COLLATE Chinese_PRC_CI_AS  AS VARCHAR(8000)) 
           ORDER BY t1.elapsed_time
       ) 排名
FROM   t1
WHERE  inserted_datetime >= @before_day_start
       AND inserted_datetime <= @before_day_end 

是这个排序规则， 但是 执行这个命令，提示

消息 447，级别 16，状态 0，第 2 行
表达式类型 xml 对 COLLATE 子句无效

[Dear SQL(燊)]

引用 3 楼 samyp1234 的回复:

[quote=引用 2 楼 chenshanliang 的回复:]
获得数据库排序规则，如下图数据库属性页


将转换的字段设为对应的排序规则

SELECT t1.session_id session_id,
       CAST(t1.sql_text COLLATE Chinese_PRC_CI_AS AS VARCHAR(8000)) SQL语句,
       t1.elapsed_time 毫秒,
       inserted_datetime 统计时刻,
       ROW_NUMBER() OVER(
           PARTITION BY CAST(t1.sql_text  COLLATE Chinese_PRC_CI_AS  AS VARCHAR(8000)) 
           ORDER BY t1.elapsed_time
       ) 排名
FROM   t1
WHERE  inserted_datetime >= @before_day_start
       AND inserted_datetime <= @before_day_end 

是这个排序规则， 但是 执行这个命令，提示

消息 447，级别 16，状态 0，第 2 行
表达式类型 xml 对 COLLATE 子句无效
[/quote]
放外面来试下，另外排序规则要替换你数据库对应的

SELECT t1.session_id session_id,
       CAST(t1.sql_text AS VARCHAR(8000)) COLLATE Chinese_PRC_CI_AS  SQL语句,
       t1.elapsed_time 毫秒,
       inserted_datetime 统计时刻,
       ROW_NUMBER() OVER(
           PARTITION BY CAST(t1.sql_text  AS VARCHAR(8000))  COLLATE Chinese_PRC_CI_AS
           ORDER BY t1.elapsed_time
       ) 排名
FROM   t1
WHERE  inserted_datetime >= @before_day_start
       AND inserted_datetime <= @before_day_end 

[吉普赛的歌]

SELECT t1.session_id session_id,
       CAST(t1.sql_text COLLATE CHINESE_PRC_BIN AS VARCHAR(8000)) SQL语句,
       t1.elapsed_time 毫秒,
       inserted_datetime 统计时刻,
       ROW_NUMBER() OVER(
           PARTITION BY CAST(t1.sql_text  COLLATE CHINESE_PRC_BIN  AS VARCHAR(8000)) 
           ORDER BY t1.elapsed_time
       ) 排名
FROM   t1
WHERE  inserted_datetime >= @before_day_start
       AND inserted_datetime <= @before_day_end 

[薛定谔的DBA]

别瞎改排序规则，为了个查询改排序规则，影响到整个数据库。 数据库的排序规则应该是和 master、tempdb 一样 否则很容易出现不能使用临时表、临时变量、使用临时库的排序、join 等会报错。 显式指定排序规则，注意转行 Nvarchar 方便兼容