SSRS报表调用存储过程刷新数据集报错：无法更新查询字段列表

我编写了如下SQL存储过程，用于SSRS报表：

ALTER       PROCEDURE [dbo].[Transaction] 
    (
    @date_start date,
    @date_end date
    )
AS
BEGIN


with 
rntexportbatch as (
Select 
                fb.BatchId,
                rt.TransactionId,
                count(*) as line_Count
from            CxWarehouse.dbo.FinancialBatchPostingDetail fbpd
INNER JOIN      CxWarehouse.dbo.FinancialBatchPosting fb 
on              (fb.BatchPostingId = fbpd.BatchPostingId and NominalAccount='12000' )  ---and NominalAccount='12000'
INNER JOIN      CxWarehouse.dbo.FinancialBatch finb 
on              (finb.BatchId = fb.BatchId)
INNER JOIN      CxWarehouse.dbo.SystemLookup l 
on              (l.LookupReference = finb.TransactionTypeId and LookupTypeId = 42)
INNER JOIN      CxWarehouse.dbo.Financialtransaction ft 
on              (ft.TransactionId = fbpd.TransactionId)
LEFT JOIN       CxWarehouse.dbo.RentTransactionElement rte 
on              (rte.TransactionElementId = ft.EntityId)
LEFT JOIN       CxWarehouse.dbo.RentElement re 
ON              (re.ElementId = rte.ElementId)
LEFT JOIN       CxWarehouse.dbo.RentTransaction rt 
ON              (rt.TransactionId = rte.TransactionId)
Group by rt.TransactionId,fb.BatchId

)


Select
                ast.AssetId,
                ast.AssetReference,
                ast.[Agreement Description],
                ast.CompanyIds,
                rntAcc.AccountId,
                rntAcc.AccountReference,
                rntAcc.PaymentReference,
                rt.TransactionId, 
                rt.AccountId, 
                rt.TransactionDate,
                rt.TransactionTypeId,
                [Transaction Type Id Description],
                rt.PostingDate,
                rt.PeriodNumber,
                rt.Description as 'Rent Transaction Description',               
                rt.Notes,
                [ElementId Description] ,
                rntPay.BatchReference as 'Import BatchId',
                rntPay.[Import Value],
                rntexportbatch.BatchId as 'Export BatchId',
                final.Value as 'Export Value'
from            CxWarehouse.dbo.RentTransaction as rt


LEFT Join (
                Select          LookupId,
                                LookupTypeId,
                                LookupReference,
                                Description as 'Transaction Type Id Description'
                from            CxWarehouse.dbo.SystemLookup
                where           LookupTypeId = 46
        ) lu
on              rt.TransactionTypeId = lu.LookupReference
LEFT Join (
                Select          
                            te.TransactionId,
                            te.TransactionElementId,
                            te.ElementId, 
                            te.Value,
                            rntElm.Description as 'ElementId Description' 
                from        CxWarehouse.dbo.RentTransactionElement as te
                
                LEFT Join (
                                    Select * from CxWarehouse.dbo.RentElement
                            ) rntElm
                on te.ElementId = rntElm.ElementId
                
            ) final 
on      rt.TransactionId = final.TransactionId

LEFT JOIN (
                Select          
                            t.AccountId,
                            t.AccountReference,
                            t.PaymentReference, 
                            typ.Description as 'AccountType Description' 
                from        CxWarehouse.dbo.RentAccount as t
                left Join   CxWarehouse.dbo.RentAccountType as typ 
                on          t.AccountTypeId = typ.AccountTypeId
        ) rntAcc 

on      rt.AccountId = rntAcc.AccountId

Left Join (

                Select          
                            ast.AssetId,
                            AssetReference,
                            rntInf.AccountId,
                            rntInf.[Agreement Description],
                            rntInf.CompanyIds
                from        CxWarehouse.dbo.Asset as ast

                left join (
                                Select          
                                        rntAgAs.AgreementAssetId,
                                        rntAgAs.AgreementId,
                                        rntAgAs.AssetId,
                                        rntAgr.AccountId,
                                        rntAgr.[Agreement Description],
                                        rntAgr.CompanyIds
                                from    CxWarehouse.dbo.RentAgreementAsset as rntAgAs
                                left join (

                                                Select          
                                                        rntAgmt.AgreementId,
                                                        rntAgmt.AgreementReference,
                                                        rntAgmt.AgreementTypeId,
                                                        rntAgmtTyp.Description as 'Agreement Description', 
                                                        rntAgmtTyp.CompanyIds,
                                                        accEpi.AccountId 
                                                from    CxWarehouse.dbo.RentAgreement as rntAgmt
                                                LEFT JOIN   CxWarehouse.dbo.RentAgreementType rntAgmtTyp
                                                on      rntAgmt.AgreementTypeId = rntAgmtTyp.AgreementTypeId
                                                Left Join (
                                                            Select 
                                                                    rntAgEp.AgreementEpisodeId,
                                                                    rntAgEp.AgreementId,
                                                                    rntAgAc.AccountId 
                                                            from    CxWarehouse.dbo.RentAgreementEpisode as rntAgEp
                                                            Left Join   CxWarehouse.dbo.RentAgreementAccount as rntAgAc
                                                            on          rntAgEp.AgreementEpisodeId =rntAgAc.AgreementEpisodeId 
                                                            ) accEpi
                                                on      rntAgmt.AgreementId = accEpi.AgreementId 
                                            ) rntAgr
                                on              rntAgAs.AgreementId = rntAgr.AgreementId 
                            ) rntInf
                    on              ast.AssetId = rntInf.AssetId
) ast 
on          rntAcc.AccountId = ast.AccountId

Left Join (

                Select          
                            rp.PaymentId,
                            rp.BatchReference,
                            rpd.PaymentDetailId, 
                            rpd.Value as 'Import Value',
                            GeneratedTransactionId 
                from        CxWarehouse.dbo.RentPayment as rp
                left Join   CxWarehouse.dbo.RentPaymentDetail rpd
                on          rp.PaymentId = rpd.PaymentId
                left join   CxWarehouse.dbo.RentPaymentPosting rpp
                on          rpd.PaymentDetailId = rpp.PaymentDetailId
                
) rntPay
on          rt.TransactionId = rntPay.GeneratedTransactionId
LEFT JOIN   rntexportbatch
on          rntexportbatch.TransactionId = rt.TransactionId

where       rt.PostingDate between @date_start  and @date_end 

END

将该存储过程用于SSRS报表后，点击报表的数据集选项并刷新数据集时，出现错误："Could not update a list of fields for the query. An item with same key has already been added"。查阅相关论坛得知该错误通常由重复列名导致，但检查存储过程代码后未发现重复列名，请求协助定位问题所在。

问题定位与修复 #

核心问题：重复列名未加别名

观察主查询的SELECT语句，你同时选取了两个未加别名的AccountId列：

rntAcc.AccountId,
-- ...
rt.AccountId, 

这两个列在返回的结果集中都以AccountId作为列名，SSRS在解析字段列表时会识别为重复键，触发报错。

修复方案

给其中一个AccountId列添加唯一别名，比如：

rntAcc.AccountId as RentAccountId,
-- ...
rt.AccountId as TransactionAccountId, 

其他排查点（可选）

• 检查子查询中是否使用SELECT *引入隐藏重复列：比如你的rntElm子查询用了Select * from CxWarehouse.dbo.RentElement，若该表与关联表有同名列，后续若不小心引入主查询会导致重复，建议显式指定所需列。
• 检查列名大小写/空格：SSRS对列名大小写不敏感，且会忽略首尾空格，比如AccountId和accountid、'AccountId '和'AccountId'都会被视为同一列。

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