Last updated on August 7th, 2022 at 09:34 pm
Here’s the error message that I kept getting, “Could not update a list of fields for the query. Verify that you can connect to the data source and that your query syntax is correct.” Within the detailed part of the error message it said, “An item with the same key has already been added.” This happened when I pasted my working query from SQL Server Management Studio (SSMS) into Visual Studio 2015 – Business Intelligence also known as SSDT-BI. This blog post will discuss this error and why using a SQL Server alias for column names is important when solving this problem.
How could I be getting this error message when the query worked perfectly in SSMS?
Troubleshooting the Error Message
Trying to solve this issue I copied the query, again, to SSDT-BI. I closed and reopened SSDT-BI and I rebooted my computer. That didn’t work.
I use templates for all of my reports. These templates already have a defined dataset within them, so normally I only update the dataset by deleting and refreshing the columns. I couldn’t figure out what was going on, so in this instance, I took drastic measures. I never, ever have to do this, but I recreated both the dataset and the data source!
In the end, none of these troubleshooting steps helped.
It was mid-morning when I was looking at this item, so I decided to step away from my computer and get a coffee. During this time, I kept mulling over the error message. The part of the error message that I kept going back to was, “Same Key.”
I kept thinking that this meant there was an issue with my JOINS, but I couldn’t see how that would be an issue when the query worked perfectly in SSMS.
When I got back to my desk, I double and triple-checked my JOINS and they worked perfectly.
SQL Server Alias
Then something hit me! The SELECT section of my query looked like the one below:
Do you see the problem? Look really closely.
Did you see that three of the columns use, “DisplayName?”
This was the problem! I was not following SQL Server guidelines on how to create a query. When I imported my query, SSDT-BI tried and failed to create three different column names called, “DisplayName.”
Here’s the lesson:
Trying to do something quickly ended up costing me more time because I didn’t bother to setup the SQL Server alias for the column names.
Fortunately the fix for this was really simple! I added three unique column aliases to my query. Taking the time to edit my query and follow SQL Server query writing guidelines made sure that everything worked perfectly!
Below is my final query SELECT section. Notice the SQL Server alias for the column names?
This shows why it is important to follow SQL Server query writing guidelines. They are designed to help save you time and effort. Following these guidelines will save you headaches in the long run!
If you have any questions, please feel free to contact me @GarthMJ.
Do you have an idea for a blog post about a Configuration Manager query or reporting topic? Let me know. Your idea might become the focus of my next blog post!