Today as I was updating an embedded T-SQL query into a dataset:
Select Something
From Somewhere
Where Something.SomethingElse = @paramSomething
And Something.SomethingElseAgain = @paramsomething
And Something.SomethingDifferent = @paramAnotherParam
I came across this gem of an error message from SSRS:
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. The variable name '@paramsomething' has already been declared. Variable names must be unique within a query batch or stored procedure. Must declare the scalar variable "@paramAnotherParam". To continue without updating the fields list, click OK.
Forgive me for being pedantic, but this is exactly the kind of error message I hate. Wrong, for a start, but also vague and misleading:
- When it can't update a list of fields for the query, it suggests you verify you can connect to the data source and that your query syntax is correct: instead of actually checking to see if either of these are the case and telling you up-front. Instead, if you go down these two paths, you've just wasted your time because they're a complete red-herring.
- It says a variable has already been declared. This is kind of untrue; specifically it doesn't provide context about where the variable has been declared twice, except to say it must be unique in a query batch or stored procedure. Nope, I didn't declare anything so that can't be it.
- The final attempt at vagueness and red herrings is complaining that @paramAnotherParam hasn't been declared, when it has nothing to do with anything except being the next parameter used in the query.
What was the actual source of the problem?
SSRS is case-sensitive with parameter names, but T-SQL isn't. It is trying to create a @paramsomething when a @paramSomething already exists, and throws the above error. Not that I care: it only took a minute to find it because I'm picky about capitalisation myself. Still, it's annoying that things aren't perfect the way I wish they were