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

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.

About these ads

99 Responses to “SSRS Quick Tip – An item with the same key has already been added”

  1. Benabdallah Says:

    Thanks,
    Have this problem yesterday give me some nightmare :)
    Very helpful

  2. Arun Says:

    Thanks this saved me lot of headache

  3. Dustin Says:

    Thanks for posting. saved my some head scratching myself.

  4. Korez73 Says:

    Thanks … saved me a hassle .. nice descriptive error Microsoft …

  5. James Says:

    Worked for me too! Cheers for that.

  6. Ron Mcvicar Says:

    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.

    • m4f1050 Says:

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

  7. Lakshmi Says:

    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

  8. Mase Says:

    Thanks, was pulling my hair out about this

  9. Deron Says:

    Thanks so much! You saved me God knows how much headache and wasted time!

  10. pmp07 Says:

    Wow… this really saved me. Thanks for sharing!!

  11. Ravi Says:

    Thanks for the info. It was really helpful. Saved me a lot of time.

  12. krithz Says:

    Thanks a ton for this…would never have figured it out! :)

  13. Jeff Says:

    Thank you so so much! (^_^)

  14. Terry Says:

    Whoa, I thought I would be at this for days. Thank you, thank you, thank you for posting.

  15. Shanon Says:

    This was exactly what I needed to fix same issue! Thanks!

  16. Chuck Richards Says:

    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

  17. HY Says:

    thank you

  18. Krishna Says:

    Thanks man . It helps Really…..

  19. David Bridge Says:

    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

  20. Archis Davda Says:

    thanks man really helped

  21. Aaron Says:

    Helped me tremendously!! Thanks

  22. Chad Renstrom Says:

    Thanks for translating the error!

  23. jevitell Says:

    Wao!! That was amazing, I solve my problem!! Thanks.

  24. vilas Says:

    Thanks……..

  25. Jen Says:

    Thanks, needed exactly this today.

  26. Diana Bodell Says:

    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!

  27. Erika Bricker (@DancesWChickens) Says:

    Thanks! I had done the same thing – inadvertently duplicated fields in my query.

  28. Doug Says:

    Thank you very much. With your post it made it much easier to figure out what was wrong.

  29. aethernw Says:

    Thank You, Thank You, Thank you …and may I add…. Thank You! We’ve been scratching our heads over this for days now. Your post was a lifesaver

  30. m Says:

    Yes, this was very helpful. Thanks for posting it. Thank you!

  31. Per Says:

    Thanks! That saved my a headache and a couple of hundred dollars in consulting fees!

  32. Daniel Says:

    Great Post! This helped me quickly!

    Thank you so much

  33. Swapnil Patre Says:

    Thank You….Very Much……….

  34. arjun Says:

    not really helped this one …………….is there any other alternative for this to handle

  35. gouri Says:

    Thanks a ton !! It saved me lot of time

  36. Martin Says:

    Legend! Saved me a migraine.

  37. Walter Says:

    You are the hero!

  38. Niall Says:

    I can’t believe I got caught out by the two identical column names from the different tables, great quick tip

    Thanks

  39. Kautilya Says:

    Thanks.

  40. hanshad Says:

    Very helpful..
    Thank you so much..

  41. Anusha Says:

    Very helpful… Thanks a lot.,,.

  42. Gilles Says:

    I was going crazy, thanks for the quick tip!

  43. Joey Says:

    Cool, thoughtful, useful post. Thanks a ton!

  44. m4f1050 Says:

    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.

    • m4f1050 Says:

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

      SELECT @test,@test2

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

  45. bkerr1080 Says:

    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!

  46. Stewart Says:

    Thank you.

  47. Anonymous Says:

    Thanks for the help.

  48. Pat Says:

    You are the man!!! :)

  49. Bob W. Says:

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

  50. Scott Says:

    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!

  51. Des Says:

    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.

  52. Bailey Says:

    THANK YOU.
    You just saved me a whole bunch of time being confused.

  53. Madhusudhan Says:

    Good stuff …….Thanks Alot Problem solved……………..

  54. John Says:

    Thanks! Just ran into the same thing.

  55. Sue Says:

    Thank you so much! In my case I had got unnamed field from aggregates … doh!

  56. Arlene Gray Says:

    Hey Robert! I ran into this today, Google search found your blog. I was like, “HEY! I know you!” Thanks for the help.

  57. Naseer Faroo Says:

    Yes, I faced the same problem and found that I also have two fields without any Alias.

  58. avi Says:

    good stuff…. thx mate

  59. Cris Kurt Says:

    This was a great help to me also! Exactly what was causing my problem.

  60. amir Says:

    tankssssssssssss
    goood
    kiss you

  61. Luis Fenrnado Says:

    Super, ese era el error! Gracias

  62. Kerry Says:

    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!

  63. LZ Says:

    Thank you so much. It could have cost me a couple of hours to figure it out.

  64. Robert Says:

    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.

  65. Assy Says:

    Thanks bragh

  66. SD Says:

    Thanks and please continue posting …

  67. ubaidbunerybaid Says:

    thanx it’s very helpfull for me.

  68. Johnzo Says:

    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.

  69. PierrieK Says:

    Awe Ma se kinners, help me alot.

  70. Chris Says:

    Thanks, why is SSRS 2008 SO much more awkward than 2005!!

  71. loadtheark Says:

    This just saved me a ton of time. Thanks for sharing.

  72. henski Says:

    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.

  73. Akash Says:

    Thank you so much

  74. Laurel Says:

    Just had this same problem and this post helped me resolve the issue super fast! Thanks!!!!

  75. Laura Says:

    Awesome, thanks!

  76. Lourdes Says:

    Excellent response it fixed my report right away.

    Lourdes
    Miami, Florida

  77. Chris Says:

    You da MAN!!!! Thanks very much!

  78. Doug, Melbourne Says:

    Ace sorted my problem thanks mate

  79. Rodney Says:

    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′

  80. Alex Says:

    fixed it for me to, thank you!

  81. Penny Says:

    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.

  82. Rebbeca Says:

    Best of all, because vacuum trucks remove debris and water, it is very unlikely to
    further injure a victim during rescue operations, something that
    is always a risk when attempting to handle all of this by hand.

    Properly inflated tires also result in lower emissions, longer tire life, enhanced safety and improved vehicle performance.
    Since businesses utilize different kinds of depreciable assets and amortizable expenses, the
    Financial Accounting Standards Board deemed it best
    to institute specific GAAP accounting rules for capitalizing costs.

  83. lvergo Says:

    Thanks! saved me hours of researching.

  84. lakshmi Says:

    Thank you very much. That was real easy fix for me.

  85. Danny Says:

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


Leave a Reply

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

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 )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 101 other followers

%d bloggers like this: