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

reporting services error log
ssrs reporting services error
ssrs subscription error log
ssrs error
ssrs an item with the same key has already been added
ssrs enable remote errors
ssrs error 137
an item with the same key has already been added. report builder

I'm getting the following error in SSRS, and it's been puzzling me a while now:

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)?

By the mornin, 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'''

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.

Errors and Events Reference (Reporting Services), The Reporting Services log files also contain error information. For more information about the types of log files that are available and how to  received this answer from stackoverflow which helped me find info on the errors: You need to look in the "Report Server Execution Log" which is a particular SQL Reporting Services log file. See Reporting Services Log Files for details.

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.

The permissions granted to user ' are insufficient for performing this , Can't update a list of fields for the query SSRS? Error handling in SQL Server gives us control over the Transact-SQL code. For example, when things go wrong, we get a chance to do something about it and possibly make it right again. SQL Server error handling can be as simple as just logging that something happened, or it could be us trying to fix an error.

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

In SSRS, why do I get the error "item with same key has already , . Solution: Click "Folder Setting" > "New Role Assignment" Then type "User\User" in the 'Group or user name text box'. Check the Roles check boxes that you would want the user to have. [Microsoft SQL Server Native Client 10.0]: A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections.

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)

Avoid Divide By Zero Error on SSRS, 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. Return Value When called in a CATCH block, ERROR_MESSAGE returns the complete text of the error message that caused the CATCH block to run. The text includes the values supplied for any substitutable parameters - for example, lengths, object names, or times. ERROR_MESSAGE returns NULL when called outside the scope of a CATCH block.

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

How can I customise error messages in SSRS?, I have build a report with the expression Amount Sales / Quota to show how well did we meet our goals. image. After running the report I got a #Error on  Stack Overflow for Teams is a private, secure spot for you and your coworkers to find and share information. Learn more SQL Server reports 'Invalid column name', but the column is present and the query works through management studio

After using Lookupset in SSRS report getting error while open report , I have SSRS Report being accessed from a Report Server. Is there any way I can give an error message of my own, if my report fails to open  I'm using SQL Server 2005. I created a stored procedure which works most of the time, but I found an instance of where it doesn't do what I want. Currently, the code does something like this if @@

Finding what the report subscription error is in SSRS, I keep getting an error "This report can't upload because there are restrictions with SQL Server Reporting Services reports when Report Definition Language  Like we alluded to above, the 500 Internal Server Error is a server-side error, meaning the problem probably isn't with your computer or internet connection but instead with the website's server. While not probable, it is possible that there's something wrong on your end, in which case we'll look at some things you can try: Reload the web page.

Modifying the Execution TimeOut Setting in Microsoft Reporting , received this answer from stackoverflow which helped me find info on the errors: You need to look in the "Report Server Execution Log" which is a particular SQL  4) Since the administrator group membership tokens are not associated with the current login, you will get a login failure with the message “server access validation failed with an infrastructure error ” Here is a side by side comparison of the tokens associated with the Windows Account or login in question.

Comments
  • 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.
  • Appears to still be an issue in 2012.
  • @wilsjd Probably because the tools are from 2007. MS really needs to get it together with this product before people move on to something else.
  • 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.
  • Issue still applies to SSRS 2016, I had the same column selected twice by mistake and had this error.
  • What if there might be a duplicate field within the same Stored Procedure? is that possible?
  • 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.