SSRS Quick Tip – An item with the same key has already been added

SQL Server, SQL Server Reporting Services, SSRS, T-SQL 1 Minute

I was in the process of creating a new report in SQL Server Reporting Services today. I was loading my dataset from a stored procedure, and when I hit the “Refresh Fields” button I recieved the following error:

“Could not create a list of fields for the query. Verify that you can connect to the data source and that your query syntax is correct.”

When I clicked the details button I got this further information:

“An item with the same key has already been added.” Here’s a screen shot of my error.

Well this had me scratching my head, as I had made sure to run the stored procedure, and it executed with no errors. After doing some considerable research I finally found a question in the Technet forums that was tangentially related to the error. This gave me the clue to figure out what I had done.

In my stored procedure, I had inadvertantly included the same column name from two different tables. My query looked something like:

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.

As it took me a while to figure this out, tought I’d pass it along to anyone else who might be looking.

Advertisement

Published by arcanecode

Published

123 thoughts on “SSRS Quick Tip – An item with the same key has already been added”

  1. Pingback: Exectweets » arcanecode at 07/30/10 07:23:32

  7. Helped me out in that I did not just have to wait for admins to apply some SQL or SSR fix. What I did was take my dataset (SELECT) and edited it to SELECT … INTO #temp (Table) and Report Builder 3.0 immediately told me the column that was duplicate. I added a # (number) to the end of the AS (e.g., quotenumber TO quotenumber2, etc.) and the problem went away.

    Reply

    1. This is true, I could troubleshoot further doing the “INTO #temp” approach on my final SELECT statement. Thanks!

      Reply

  8. I had the same issue with my Dataset. I was breaking my head and tried all the possible solutions until I found your solution. Thanks for sharing your experience. It helped a lot.

    Thanks

    Reply

  18. Hey all….. I ran into the same problem.

    But here’s the KICKER…..

    I had the same problem because I used ‘coalesce’ on more then 1 column…..AGAIN…. MsSql handles coalesce’ing more then 1 column fine, but SSRS fails if you DO NOT DO AN ‘AS blah’ on each coalesce… seems coalesce LOSSES the identifier…. I verified this when I added 1 column with coalesce and it added my sql fine, then I looked at the column names and the column was ‘ID’… hence…. more then 1 column with the same generic ‘id’ name…….

    what a kick in the

    Reply

  21. Helped me too. Thanks to your post coming up immediately in my search, this issue only took a few minutes of head scratching. Would have been a lot more without it so thanks for this and I have to say its a very well worded article.

    In my case the procedure was correct but I had put in a debugging SELECT statement in to the query just before I went home last night and that SELECT statement contained two identical column names.

    David Bridge
    David Bridge Technology Limited
    http://davidbridge.wordpress.com

    Reply

  29. My problem was because I was doing select * instead of individually listing the fields. I had the fields aliased and renamed properly but using the asterisk caused the same problem as you described above. Listing the fields solved it. Thanks for the answer!

    Reply

  47. I had a similar issue…and further testing I found out what was causing it.. Here is an example:

    DECLARE @test INT
    SELECT @test=1

    SELECT @test

    This will not work.

    Reply

    1. DECLARE @test INT,@test2 INT
      SELECT @test=1,@test2=2

      SELECT @test,@test2

      EDIT: Sorry, forgot which one was the one with issues.

      Reply

  48. My problem was that I was using the sum() function and not aliasing the column. Your post led me straight to the answer, though. Thanks!

    Reply

  52. OMG…you saved my life. This was driving me crazy. Thanks! I wonder if MS would consider changing this “bogus” error message.

    Reply

  53. Thanks to people like you for sharing this on the web. I only struggled with this error for about 5 minutes before doing a quick search on Google and found this post. It very quickly helped me isolate my issue and got me back on track. I appreciate you taking the time to document this for the rest of us!

    Reply

  54. Thanks for the tip. The stored procedure was working fine for Crystal Reports but as soon as I tried it with SSRS no joy.

    Thanks again.

    Reply
  62. Pingback: Troubleshooting SSRS Report Error - An item with the same key has already been added - Dilip's blog on MICROSOFT DYNAMICS AX - AX Technical Blogs - Microsoft Dynamics Community
  63. Pingback: Troubleshooting SSRS Report Error – An item with the same key has already been added – 12/19, Dilip's Blog on AX | Partner Compete

  67. Excellent tip – I was referencing the same table twice to pull out different information. Worked fine in SQL Management Studio & couldn’t fathom why it wouldn’t work in Report Designer 3 until I read this. Thanks!

    Reply

  69. Big thanks for this, saved me a lot of time. As a few others have had, I had two aggregate fields without an alias, which works fine in Server Management Studio, but not for SSRS.

    Reply

  73. LOL, what an awful way to integrate stored procedures. How can Microsoft design BOTH the syntax for stored procedures AND a report writer, and not even bother linking them together better than this? Pa-thetic. I just finished writing a ‘wrapper’ stored procedure that creates a temporary table, and fills it with the output of ANOTHER stored procedure. Just so SSRS is happy.

    Reply

  77. This issue gave me a headache for half an hour until i decided to paste the detail on google and found your post. Thanks man! You just cured me instantly, you’re my savior.

    Reply

  84. Your research triggered a solution to a similar problem I had. My dataset would not connect when I had an MSSQL stored proc that had compiled.

    My problem was I had variables as columns in my final dataset.

    SELECT @value1,
    @value2,
    @value3

    I gave each field an alias and my code worked fine.

    SELECT @value1 as ‘value1’,
    @value2 as ‘value2’,
    @value3 as ‘value3’

    Reply

  86. Thank you so much – I had obviously been over zealous with my copy and pasting… But you were right 2 columns with the same name. Cheers for posting the solution.

    Reply

  87. Thanks for sharing. My issue was because I concatenated two columns together and just removed the concatenation from the two and the error disappeared.

    Reply

  92. I’m trying to do this:
    ;with temp as (select row_number()over(order by linenumber asc) as ‘Row’,*
    from [ATMJOURNALS].[dbo].[PPT000120141111]
    )
    SELECT * from temp t1
    inner join temp t2 on t2.Row = t1.Row+1
    where t1.word3 = t2.word3 and t2.word4 like ‘%END%’
    works on sql, can’t figure our how to make different column names for t2 so throwing above exception on ssrs, any ideas?

    Reply

  94. I am also facing that same problem, please anybody help me to solve that problem, i attach my query so where need to change,

    SELECT
    a1.CtgVarDesc,
    “Active” =
    CASE
    WHEN
    a1.CtgChrWebActive = 1 THEN ‘Active’
    END,
    “Inactive” =
    CASE
    WHEN
    a1.CtgChrWebActive = 2 THEN ‘Inactive’
    END,
    b1.CtgVarDesc,
    “Active” =
    CASE
    WHEN
    b1.CtgChrWebActive = 1 THEN ‘Active’
    END,
    “InactiveActive” =
    CASE
    WHEN
    b1.CtgChrWebActive = 2 THEN ‘Inactive’
    END,
    c1.CtgVarDesc,
    “Active” =
    CASE
    WHEN
    c1.CtgChrWebActive = 1 THEN ‘Active’
    END,
    “InActive” =
    CASE
    WHEN
    c1.CtgChrWebActive = 2 THEN ‘Inactive’
    END,
    d1.PrdVarDesc,
    “Active” =
    CASE
    WHEN
    d1.PrdChrActiveYN = ‘y’ THEN ‘Active’
    END,
    “InActive” =
    CASE
    WHEN
    d1.PrdChrActiveYN = ‘N’ THEN ‘Inactive’
    END
    FROM CbzCtgMst a1
    INNER JOIN CbzCtgMst b1
    ON (a1.CtgIntId = b1.CtgIntPtid)
    INNER JOIN CbzCtgMst c1
    ON (b1.CtgIntId = c1.CtgIntPtId)
    INNER JOIN cbzprdmst d1
    ON (c1.CtgIntId = d1.PrdIntCtgId)
    order by a1.CtgVarDesc,b1.CtgVarDesc,c1.CtgVarDesc,d1.PrdChrActiveYN

    Reply

  97. had to add column aliases and then it worked:

    SELECT fcm.Name,
    col.Name,
    adv.AdvertisementName,
    col.CollectionID

    become:

    SELECT fcm.Name as MN,
    col.Name as CN,
    adv.AdvertisementName,
    col.CollectionID

    Reply

  98. Thanks man…Saved me from a lot of heartburn…The error message doesn’t give you any hint on where you should look at…

    Reply

  99. thanks for your post. I can see lots of people problem got solved with your solution. but I am still struggeling.
    I have a table with woId column in it and have a view that pulls out data and so some calculation on each field from the same table, therefore the view again give me a table with woId column and I am joining the table from view and Table on the woId column.
    so what you said is that I need to rename the column in my table? I know that I will mess up with so many things if I change a column name in that table.
    do aliasing help here?

    i really appreciate any help.

    Reply

  106. This article helped me to resolve my problem
    Thanks for publishing and appreciate educating the community.

    I had two tables t1 and t2 in my query then my select statement had t1.name, t2.name
    Query worked fine in SSMS
    This query I out it in Stored Procedure and procedure worked fine in SSMS
    But When I choose this procedure in SSRS reporting for creating data set then it erored out what you faced the problem.
    SSRS is unable to under stand prefixes carried in sql stmt or stored proc hence we have to clearly specify distinct name for SSRS fields

    Thanks a lot

    Reply

  110. THANKS! Still using this app so this was very helpful, especially the Ron Mcvicar suggestion to select into #temp!

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

Gravatar
WordPress.com Logo

You are commenting using your WordPress.com account. ( Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. ( Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. ( Log Out /  Change )

Cancel

Connecting to %s