In SSRS, why do I get the error "item with same key has already been added" , when I'm making a new report?

Caffeinated picture Caffeinated · Jan 22, 2013 · Viewed 105.5k times · Source

I'm getting the following error in SSRS:

An error occurred while the query design method was being saved.
An item with the same key has already been added

What does an "item" denote, though? I even tried editing the RDL and deleting all references to the Stored Procedure I need to use called prc_RPT_Select_BI_Completes_Data_View.

Could this possibly have to do with the fact that the Stored Procedure uses Dynamic SQL (the N' notation)?

In the stored procedure I have:

SET @SQL +=  N'
SELECT   bi.SupplierID as ''Supplier ID''
        ,bi.SupplierName as ''Supplier Name''

        ,bi.PID as ''PID''
        ,bi.RespondentID as ''Respondent ID''

        ,lk_slt.Name as ''Entry Link Type''

        ,ts.SurveyNumber as ''Initial Survey ID'''

enter image description here

Answer

Caffeinated picture Caffeinated · Jan 22, 2013

It appears that SSRS has an issue(at leastin version 2008) - I'm studying this website that explains it

Where it says if you have two columns(from 2 diff. tables) with the same name, then it'll cause that problem.

From source:

SELECT a.Field1, a.Field2, a.Field3, b.Field1, b.field99 FROM TableA a JOIN TableB b on a.Field1 = b.Field1

SQL handled it just fine, since I had prefixed each with an alias (table) name. But SSRS uses only the column name as the key, not table + column, so it was choking.

The fix was easy, either rename the second column, i.e. b.Field1 AS Field01 or just omit the field all together, which is what I did.