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'''
In the Report Data pane, right-click the dataset, and then click Query. Click Refresh Fields.
Right click Reports and add an existing item. Select an existing RDL file to add it to the project. Double click it to open it. You will now be able to see and edit the report in Visual Studio.
While creating any SSRS report using stored procedure, you will first create dataset for it. Select stored procedure in dropdown and click refresh fields.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With