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

Question

I get the following error in SSRS , and this has puzzled me now:

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

What does "element" mean though? I even tried to edit the RDL and delete all references to the stored procedure. I need to use the called prc_RPT_Select_BI_Completes_Data_View .

Perhaps this is due to the fact that the stored procedure uses Dynamic SQL (symbol N' )?

In the mornings in the stored procedure I:

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

  ,bi.PID as ''PID'' ,bi.RespondentID as ''Respondent ID'' ,lk_slt.Name as ''Entry Link Type'' ,ts.SurveyNumber as ''Initial Survey ID''' 

Answer 1

It seems that SSRS has a problem (at least in version 2008) - I am exploring this website that explains this

Where he says, if you have two columns (from two difference tables) with the same name, this will cause this 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, as I had a prefix with an alias (table). But SSRS only uses the column name as the key, not the + table, so it was suffocating.

The fix was simple, or rename the second column, i.e. b.Field1 AS Field01 or just lower the field together, which I did.

Answer 2

I have experience in the past. Based on this, I can say that in general we get this problem if your dataset has several fields that point to the same field source. Take a look at the following posts for detailed errors.

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 all the field names returned by Sp prc_RPT_Select_BI_Completes_Data_View and make sure that all fields have a unique name.

Answer 3

I just got this error and I found out that it is a local variable alias

at the end of the stored procedure I liked

 select @localvariable1,@localvariable2 

it worked fine in sql, but when I ran it in ssrs it was always an error, but after I gave the alias, it was fixed.

 select @localvariable1 as A,@localvariable2 as B