One of the darling phrases of any data professional, alongside classics such as "it depends", is "that is by design", best deployed deadpan whenever confronted with inexplicable or eccentric data-centric behavior, in a piece of software or code. I seem to have been deploying the phrase a lot recently.
A while back, I was called in to help a customer fix what they felt must be a "permissions issue" with a stored procedure that accepted a multi-valued parameter from an SSRS report. I examined the malfunctioning procedure and was surprised to see that they were using it not to return data to the report, but to modify data in the source database!
The DBA in me was worried. Does SSRS really work this way "by design"? Surely not! The detective in me was intrigued. I quickly spotted an error in their code; the query type was set to Text instead of Stored Procedure. I made the fix and, voila, the report modified the data exactly as they intended.
The customer had the solution they needed, though I did my best to warn them of the possible security and data integrity ramifications of this approach, and recommended that they should build an application for this, and then tie in a report via Iframe or a ReportViewer Control.
Later, I mused on the fact that, however pressing and urgent the need to find a solution, one must understand the risks involved. Does a feature work that way, even if that way seems inexplicable, "by design", or are you relying on behavior that is "not guaranteed" and so could easily change in future versions?
If it's by-design, then Microsoft is obliged to support that way of doing it in future versions, even if they also introduce a new and better way. I have firsthand experience of the pains of trying to implement a many-to-many relationship in an Excel 2013 data model. By design, there was just no easy way to do it. Now, in Excel 2016, with bidirectional filters, it's a breeze, but the older, more complex ways, using DAX and cross table filtering and the like, will still work. You won't get left with broken code.
This SSRS case felt different. I was certain Microsoft would not approve of SSRS being used in this way. If you're relying on behaviour that is not guaranteed, then, essentially, the success of your solution relies on the persistence of a 'bug'. The "quirky update" is another good example of this. It relies on unsupported behavior, and yet many people use it, and Microsoft has continued to support it across many SQL Server versions.
So, had I done more harm than good in fixing this customer's SSRS solution? Is it ever wise to bend a feature to work in a way it wasn't intended to get the solution you need, knowing that if Microsoft removed this "design flaw" from a future version of SQL Server, it would leave you with a solution in purgatory, from which you cannot upgrade without a ton of rework? It depends. Which probably explains why so many people, to Microsoft s horror, seem still to be running applications that rely on SQL Server 2005.
Rodney Landrum (Guest Editor).
