SSRS报varchar转real类型错误 相同查询在SSMS运行正常

问题说明 #

相同T-SQL查询在SSMS中可正常执行，迁移到SSRS并新增部分列后触发将varchar数据类型转换为real数据类型时出错报错，手动显式转换SUM涉及的float列后问题仍存在。
附原始查询（已脱敏）：

select x.* ,
dbo.wc_calculateRateDecimal([Num Accepted],[Num Of Students]) as 'Percentage Usage'
from
(SELECT mainSct_id,
       Count(stu_id) AS 'Num Of Students',
       Sum(CASE
      WHEN COALESCE(eng.[Ts And Cs Accepted], 'No') = 'Yes' THEN 1
     ELSE 0
   END  )    AS 'Num Accepted',
    sum(eng.[Connection Requests]) as [Connection Requests],
    sum(eng.[Direct Messages])as[Direct Messages],
    sum(eng.[Group Posts])as [Group Posts],
    sum(eng.[Profile Posts])as [Profile Posts]
FROM   REDACTED stu
       LEFT OUTER JOIN (select * from REDACTED  where [Ts And Cs Accepted] ='Yes')eng 
                    ON stu.REDACTED = eng.REDACTED
                       AND stu.REDACTED = eng.REDACTED
WHERE  acy_id = '21/22'
       AND mainCrs_catType != 'APP'
       AND mainCrsProgress_id IN ( 'A', 'C', 'F' )
       AND mainCrs_catType = 'POS'

根因预判 #

SSRS与SSMS的查询解析规则、默认连接会话参数存在差异，SSMS兼容大量非标准SQL写法，SSRS的解析器校验逻辑更严格，会提前推导查询元数据，很容易命中SSMS不会触发的隐式转换分支；另外原始查询存在语法缺失、非标准写法，会进一步放大解析差异。

分步修复方案 #

• 第一步：补全查询语法，修正非标准别名写法
  原始查询存在两个明显的语法问题，SSMS可兼容但SSRS解析会出错：
  1. 内层聚合查询缺失GROUP BY子句，使用了Count/Sum聚合函数但未按非聚合列mainSct_id分组
  2. 部分列别名使用单引号包裹，单引号是字符串常量标识，SSRS会将这类别名误识别为varchar类型的常量值，直接触发类型转换错误，所有列别名需统一用方括号[]包裹
     修正后的基础查询框架如下：

SELECT x.* ,
dbo.wc_calculateRateDecimal([Num Accepted],[Num Of Students]) as [Percentage Usage]
FROM
(
    SELECT 
        mainSct_id,
        Count(stu_id) AS [Num Of Students],
        Sum(CASE
            WHEN COALESCE(eng.[Ts And Cs Accepted], 'No') = 'Yes' THEN 1
            ELSE 0
        END) AS [Num Accepted],
        sum(eng.[Connection Requests]) as [Connection Requests],
        sum(eng.[Direct Messages]) as [Direct Messages],
        sum(eng.[Group Posts]) as [Group Posts],
        sum(eng.[Profile Posts]) as [Profile Posts]
    FROM REDACTED stu
    LEFT OUTER JOIN (
        -- 禁止使用select *，显式列出需要的字段，避免冗余字段引发列名歧义
        SELECT [REDACTED], [REDACTED], [Ts And Cs Accepted], [Connection Requests], [Direct Messages], [Group Posts], [Profile Posts] 
        FROM REDACTED  
        WHERE [Ts And Cs Accepted] ='Yes'
    ) eng 
    ON stu.REDACTED = eng.REDACTED AND stu.REDACTED = eng.REDACTED
    WHERE  acy_id = '21/22'
        AND mainCrs_catType != 'APP'
        AND mainCrsProgress_id IN ( 'A', 'C', 'F' )
        AND mainCrs_catType = 'POS'
    -- 补全缺失的分组逻辑
    GROUP BY mainSct_id
) x

• 第二步：全链路消除隐式转换
  不要仅转换SUM涉及的float列，重点校验三个易触发转换的位置：
  1. 自定义函数入参：检查dbo.wc_calculateRateDecimal的两个入参定义类型，如果是real/numeric等数值类型，显式对传入值做类型转换，写法参考：dbo.wc_calculateRateDecimal(CAST([Num Accepted] AS real), CAST([Num Of Students] AS real))
  2. 关联字段类型：检查两表关联用的REDACTED字段类型是否一致，若一侧为varchar一侧为数值类型，显式做类型统一，避免关联逻辑触发隐式转换
  3. 聚合字段类型：虽然SUM涉及的基础列是float类型，但LEFT JOIN场景下eng表的字段可能返回NULL，可显式套一层ISNULL(字段,0)再做SUM，避免NULL值推导类型异常
• 第三步：统一连接会话参数
  SSMS默认开启ARITHABORT参数，SSRS数据源连接默认关闭该参数，会导致执行计划生成逻辑差异。打开SSRS对应数据源的连接字符串，在末尾追加;ARITHABORT=ON，和SSMS的会话配置保持一致。
• 第四步：清理SSRS元数据缓存
  新增列后SSRS会缓存旧的数据集字段类型，进入对应数据集的「字段」配置页，删除所有已生成的字段后重新刷新字段列表，避免旧缓存的类型映射（比如将某列缓存为real类型但实际返回varchar）触发转换错误。

内容的提问来源于stack exchange，提问作者Lewis C