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.