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

July 2010
S	M	T	W	T	F	S
« Jun		Aug »
	1	2	3
4	5	6	7	8	9	10
11	12	13	14	15	16	17
18	19	20	21	22	23	24
25	26	27	28	29	30	31

July 30, 2010 — arcanecode

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.

Posted in SQL Server, SQL Server Reporting Services, SSRS, T-SQL. Tags: SQL Server, SQL Server Reporting Services, SSRS, T-SQL. 45 Comments »

Exectweets » arcanecode at 07/30/10 07:23:32 Says:
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 [...]

Chetan S Says:
May 5, 2011 at 5:12 pm
It worked !! Thanks

Benabdallah Says:
August 13, 2010 at 2:09 am

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

Arun Says:
September 23, 2010 at 12:35 am

Thanks this saved me lot of headache

Dustin Says:
December 2, 2010 at 9:46 am

Thanks for posting. saved my some head scratching myself.

Korez73 Says:
March 21, 2011 at 10:30 am

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

James Says:
April 12, 2011 at 4:38 am

Worked for me too! Cheers for that.

Ron Mcvicar Says:
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.

Lakshmi Says:
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

Mase Says:
May 10, 2011 at 12:29 am

Thanks, was pulling my hair out about this

Deron Says:
May 19, 2011 at 12:54 am

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

pmp07 Says:
May 26, 2011 at 1:16 pm

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

Ravi Says:
June 8, 2011 at 8:29 am

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

krithz Says:
June 29, 2011 at 10:03 am

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

Jeff Says:
July 6, 2011 at 6:02 am

Thank you so so much! (^_^)

Terry Says:
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.

Shanon Says:
August 9, 2011 at 11:02 am

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

Mark H from Sydney, AUS Says:
August 22, 2011 at 2:54 am

kudos mate

Chuck Richards Says:
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

HY Says:
August 25, 2011 at 4:27 pm

thank you

Krishna Says:
August 31, 2011 at 7:35 am

Thanks man . It helps Really…..

David Bridge Says:
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

Archis Davda Says:
September 19, 2011 at 12:08 pm

thanks man really helped

Aaron Says:
September 25, 2011 at 7:52 am

Helped me tremendously!! Thanks

kimsteinmetz Says:
September 27, 2011 at 1:24 pm

thanks, me too!!!

Chad Renstrom Says:
September 30, 2011 at 1:17 pm

Thanks for translating the error!

jevitell Says:
October 6, 2011 at 9:32 am

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

vilas Says:
October 12, 2011 at 12:03 am

Thanks……..

Jen Says:
October 21, 2011 at 2:49 pm

Thanks, needed exactly this today.

Diana Bodell Says:
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!