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

Question

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'''

Edin

Answer 1

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.

Edin

You will get the same issue if SP/SQL returns duplicate column name. Becuase main problem with the SSRS that throws error in this case. – Squatness

Appears to still be an issue in 2012. – Lassalle

@Lassalle Probably because the tools are from 2007. MS really needs to get it together with this product before people move on to something else. – Decennial

You get this issue if you forget to give more than one field names, such as calculated fields. SQL returns "No column name" and SSRS is unhappy with two fields named "No column name". It's exactly the same issue as duplicate column names, but took my eyes a little longer to spot. – Sheathing

Issue still applies to SSRS 2016, I had the same column selected twice by mistake and had this error. – Azide

It's 2021 and I'm still an issue in ssrs – Lovelorn

Still an issue in 2022. Absolutely ridiculous. – Rajewski

this worked for me. im getting data from a SP and there were two columns with same name and removed one . then it worked – Wolsey

Answer 2

I face the same issue. After debug I fixed the same. if the column name in your SQL query has multiple times then this issue occur. Hence use alias in SQL query to differ the column name. Ex: The below query will work proper in sql query but create issue in SSRS report:

Select P.ID, P.FirstName, P.LastName, D.ID, D.City, D.Area, D.Address
From PersonalDetails P
Left Join CommunicationDetails D On P.ID = D.PersonalDetailsID

Reason : ID has mentioned twice (Multiple Times)

Correct Query:

Select P.ID As PersonalDetailsID, P.FirstName, P.LastName, D.ID, D.City, D.Area, D.Address
From PersonalDetails P
Left Join CommunicationDetails D On P.ID = D.PersonalDetailsID

Deli

Answer 3

I have experience this issue in past. Based on that I can say that generally we get this issue if your dataset has multiple fieldnames that points to same field source. Take a look into following posts for detail error description

https://bi-rootdata.blogspot.com/2012/09/an-error-occurred-during-report.html

https://bi-rootdata.blogspot.com/2012/09/an-item-with-same-key-has-already-been.html

In your case, you should check your all field names returned by Sp prc_RPT_Select_BI_Completes_Data_View and make sure that all fields has unique name.

Squatness

What if there might be a duplicate field within the same Stored Procedure? is that possible? – Edin

If there will be duplicate fields returned by SP and you bind all the fields with report fields, then you will get the same issue. – Squatness

Answer 4

I had the same error in a report query. I had columns from different tables with the same name and the prefix for each table (eg: select a.description, b.description, c.description) that runs ok in Oracle, but for the report you must have an unique alias for each column so simply add alias to the fields with the same name (select a.description a_description, b.description b_description and so on)

Pyrogen

Answer 5

Sorry, it is a response to an old thread, but might still be useful.

In addition to above responses, This generally happens when two columns with same name, even from different tables are included in the same query. for example if we joining two tables city and state where tables have column name e.g. city.name and state.name. when such a query is added to the dataset, SSRS removes the table name or the table alias and only keeps the name, whih eventually appears twice in the query and errors as duplicate key. The best way to avoid it is to use alias such as calling the column names city.name as c_name state.name as s_name. This will resolve the issue.

Excellence

Answer 6

I got this error message with vs2015 enterprise, ssdt 14.1.xxx, ssrs. For me I think it was something different than described above with a 2 column, same name problem. I added this report, then deleted the report, then when I tried to add the query back in the ssrs wizard I got this message, " An error occurred while the query design method was being saved :invalid object name: tablename" . where tablename was the table on the query the wizard was reading. I tried cleaning the project, I tried rebuilding the project. In my opinion Microsoft isn't completing cleaning out the report when you delete it and as long as you try to add the original query back it won't add. The way I was able to fix it was to create the ssrs report in a whole new project (obviously nothing wrong with the query) and save it off to the side. Then I reopened my original ssrs project, right clicked on Reports, then Add, then add Existing Item. The report added back in just fine with no name conflict.

Muncey

Answer 7

I just got this error and i came to know that it is about the local variable alias

at the end of the stored procedure i had like

select @localvariable1,@localvariable2

it was working fine in sql but when i ran this in ssrs it was always throwing error but after I gave alias it is fixed

select @localvariable1 as A,@localvariable2 as B

Corticosterone

Answer 8

SSRS will not accept duplicated columns so ensure that your query or store procedure is returning unique column names.

Mulberry

Answer 9

If you are using SPs and if the sps have multiple Select statements (within if conditions) all those selects needs to be handled with unique field names.

Chandrachandragupta