使用OPENQUERY对接Global Shop Solutions/Actian后端时的精度错误

问题场景 #

从后端为Actian的Global Shop Solutions（GSS）通过OPENQUERY执行聚合查询，将数据导入SQL Server 2019时，出现以下错误：

Msg 7354, Level 16, State 1, Line 54
链接服务器"GSS"的OLE DB提供程序"MSDASQL"为列"GSS_Total"提供的元数据无效，精度超过允许的最大值。

移除SUM()语句后错误消失，问题出在聚合后的数值类型精度传递环节。

原始查询代码：

select 
    * 
from 
    openquery(GSS, '
                    select 
                        h.purchase_order
                        , l.part
                        , l.record_no
                        , sum(l.qty_received) GSS_Total
                        , max(l.date_last_received) date_last_received
                    from 
                        v_po_header h 
                        inner join v_po_lines l on h.purchase_order = l.purchase_order
                    where
                        l.part like ''??-*''
                        and l.flag_recv_close <> ''Y''
                        and h.flag_recv_closed <> ''Y''
                        and h.type = 0
                        and substring(l.part, 17, 1) = ''''
                    group by
                        h.purchase_order
                        , l.part
                        , l.record_no
                    ') t

解决办法 #

在Actian的查询语句中，显式将sum(l.qty_received)转换为精度符合SQL Server要求的数值类型，比如decimal(18,2)（可根据实际业务数据调整精度和小数位数）。

修改后的查询代码：

select 
    * 
from 
    openquery(GSS, '
                    select 
                        h.purchase_order
                        , l.part
                        , l.record_no
                        , sum(cast(l.qty_received as decimal(18,2))) GSS_Total
                        , max(l.date_last_received) date_last_received
                    from 
                        v_po_header h 
                        inner join v_po_lines l on h.purchase_order = l.purchase_order
                    where
                        l.part like ''??-*''
                        and l.flag_recv_close <> ''Y''
                        and h.flag_recv_closed <> ''Y''
                        and h.type = 0
                        and substring(l.part, 17, 1) = ''''
                    group by
                        h.purchase_order
                        , l.part
                        , l.record_no
                    ') t

原因说明 #

Actian数据库中，sum(l.qty_received)返回的数值类型精度可能远高于SQL Server允许的最大值（SQL Server decimal类型最大精度为38）。MSDASQL提供程序在传递该列的元数据给SQL Server时，因精度超限导致验证失败，触发7354错误。通过显式转换指定合适的精度，能让元数据正确传递，避免报错。

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