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.
August 6, 2010 at 3:04 am
[...] Pro Tweets New post: http://arcanecode.com/2010/07/30/ssrs-quick-tip-an-item-with-the-same-key-has-already-been-added/ #dontdowhatIdid #dumbthing arcanecode – Fri 30 Jul 7:23 All Things [...]
May 5, 2011 at 5:12 pm
It worked !! Thanks
September 27, 2012 at 2:20 am
Thank you I have the same problem (In my SP I put the same column twice)
that fixwd it
April 18, 2013 at 7:48 am
Thanks, this was causing me to lose sleep!!
May 12, 2013 at 5:13 pm
Thanks heaps, you saved me so much time!
August 13, 2010 at 2:09 am
Thanks,
Have this problem yesterday give me some nightmare
Very helpful
September 23, 2010 at 12:35 am
Thanks this saved me lot of headache
December 2, 2010 at 9:46 am
Thanks for posting. saved my some head scratching myself.
March 21, 2011 at 10:30 am
Thanks … saved me a hassle .. nice descriptive error Microsoft …
April 12, 2011 at 4:38 am
Worked for me too! Cheers for that.
April 12, 2011 at 12:31 pm
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.
June 13, 2012 at 11:05 am
This is true, I could troubleshoot further doing the “INTO #temp” approach on my final SELECT statement. Thanks!
April 15, 2011 at 8:58 am
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
May 10, 2011 at 12:29 am
Thanks, was pulling my hair out about this
May 19, 2011 at 12:54 am
Thanks so much! You saved me God knows how much headache and wasted time!
May 26, 2011 at 1:16 pm
Wow… this really saved me. Thanks for sharing!!
June 8, 2011 at 8:29 am
Thanks for the info. It was really helpful. Saved me a lot of time.
June 29, 2011 at 10:03 am
Thanks a ton for this…would never have figured it out!
July 6, 2011 at 6:02 am
Thank you so so much! (^_^)
July 11, 2011 at 12:14 pm
Whoa, I thought I would be at this for days. Thank you, thank you, thank you for posting.
August 9, 2011 at 11:02 am
This was exactly what I needed to fix same issue! Thanks!
August 22, 2011 at 2:54 am
kudos mate
August 23, 2011 at 9:51 am
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
August 25, 2011 at 4:27 pm
thank you
August 31, 2011 at 7:35 am
Thanks man . It helps Really…..
September 2, 2011 at 3:49 am
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
September 19, 2011 at 12:08 pm
thanks man really helped
September 25, 2011 at 7:52 am
Helped me tremendously!! Thanks
September 27, 2011 at 1:24 pm
thanks, me too!!!
September 30, 2011 at 1:17 pm
Thanks for translating the error!
October 6, 2011 at 9:32 am
Wao!! That was amazing, I solve my problem!! Thanks.
October 12, 2011 at 12:03 am
Thanks……..
October 21, 2011 at 2:49 pm
Thanks, needed exactly this today.
October 22, 2011 at 4:43 pm
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!
October 26, 2011 at 9:22 am
Thanks! I had done the same thing – inadvertently duplicated fields in my query.
November 3, 2011 at 8:32 am
Thank you very much. With your post it made it much easier to figure out what was wrong.
November 9, 2011 at 5:05 pm
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
November 16, 2011 at 11:19 am
Yes, this was very helpful. Thanks for posting it. Thank you!
November 23, 2011 at 8:18 am
Thanks! That saved my a headache and a couple of hundred dollars in consulting fees!
December 5, 2011 at 10:33 am
Great Post! This helped me quickly!
Thank you so much
December 22, 2011 at 11:14 pm
Thank You….Very Much……….
December 29, 2011 at 7:10 am
not really helped this one …………….is there any other alternative for this to handle
January 11, 2012 at 7:50 am
Thanks a ton !! It saved me lot of time
January 19, 2012 at 9:43 am
Legend! Saved me a migraine.
February 24, 2012 at 8:00 am
You are the hero!
February 25, 2012 at 8:20 am
I can’t believe I got caught out by the two identical column names from the different tables, great quick tip
Thanks
March 29, 2012 at 5:26 am
Thanks.
April 5, 2012 at 1:15 pm
Very helpful..
Thank you so much..
April 25, 2012 at 11:24 am
Very helpful… Thanks a lot.,,.
June 6, 2012 at 9:31 am
I was going crazy, thanks for the quick tip!
June 7, 2012 at 2:08 am
Cool, thoughtful, useful post. Thanks a ton!
June 13, 2012 at 10:21 am
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.
June 13, 2012 at 10:22 am
DECLARE @test INT,@test2 INT
SELECT @test=1,@test2=2
SELECT @test,@test2
EDIT: Sorry, forgot which one was the one with issues.
June 22, 2012 at 9:54 am
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!
July 2, 2012 at 11:21 pm
Thank you.
July 18, 2012 at 7:00 pm
Thanks for the help.
August 9, 2012 at 10:20 am
You are the man!!!
August 29, 2012 at 3:04 pm
OMG…you saved my life. This was driving me crazy. Thanks! I wonder if MS would consider changing this “bogus” error message.
September 5, 2012 at 7:04 am
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!
September 7, 2012 at 6:10 am
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.
September 11, 2012 at 1:08 pm
THANK YOU.
You just saved me a whole bunch of time being confused.
September 13, 2012 at 3:01 am
Good stuff …….Thanks Alot Problem solved……………..
September 18, 2012 at 4:51 pm
Thanks! Just ran into the same thing.
October 5, 2012 at 4:48 am
Thank you so much! In my case I had got unnamed field from aggregates … doh!
October 24, 2012 at 12:51 pm
Hey Robert! I ran into this today, Google search found your blog. I was like, “HEY! I know you!” Thanks for the help.
November 2, 2012 at 2:32 pm
Yes, I faced the same problem and found that I also have two fields without any Alias.
November 8, 2012 at 10:31 pm
good stuff…. thx mate
December 18, 2012 at 7:06 pm
[...] has already been added" while loading my datasets from Visual Studio. Solution: Thanks to this blog, it came to my [...]
December 18, 2012 at 8:00 pm
[...] to this blog, it came to my [...]
December 27, 2012 at 9:55 am
This was a great help to me also! Exactly what was causing my problem.
January 14, 2013 at 8:40 am
tankssssssssssss
goood
kiss you
January 22, 2013 at 2:07 pm
Super, ese era el error! Gracias
January 31, 2013 at 6:10 am
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!
January 31, 2013 at 11:20 am
Thank you so much. It could have cost me a couple of hours to figure it out.
February 1, 2013 at 1:01 pm
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.
February 1, 2013 at 1:12 pm
Thanks bragh
February 4, 2013 at 9:39 pm
Thanks and please continue posting …
February 7, 2013 at 4:25 am
thanx it’s very helpfull for me.
February 12, 2013 at 9:08 am
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.
March 6, 2013 at 1:20 am
Awe Ma se kinners, help me alot.
March 11, 2013 at 9:33 am
Thanks, why is SSRS 2008 SO much more awkward than 2005!!
March 13, 2013 at 9:48 am
This just saved me a ton of time. Thanks for sharing.
March 29, 2013 at 1:20 pm
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.
April 5, 2013 at 4:12 pm
Thank you so much
May 3, 2013 at 11:13 am
Just had this same problem and this post helped me resolve the issue super fast! Thanks!!!!