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  quot item quot  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

User · Accepted Answer

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

User · Answer

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

User · Answer

I got this error message with vs2015  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   quot  An error occurred while the query design method was being saved  invalid object name  tablename quot      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

User · Answer

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

User · Answer

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  http   www bi-rootdata com 2012 09 an-error-occurred-during-report html  http   www bi-rootdata 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

User · Answer

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

User · Answer

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

User · Answer

Sorry  it is a reponse to an old thread  but might still be usefull   In addition to above reponses  This genrally 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

User · Answer

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

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

Examples related to ssrs-2008

Examples related to reporting-services