The subject of this month’s T-SQL Tuesday blogging party, #46, is the Rube Goldberg Machine. Rube Goldberg was a cartoonist whose drawings featured contrived, complicated contraptions. The goal of the assignment is to describe a SQL Server solution you have put into place that could be called a kludge, the digital equivalent of Duct Tape, WD-40, and paper clips.
Back when I was the DBA for a large law firm, I had to support quite a few niche apps. These apps were often something that a law firm somewhere came up with that filled a particular need and were usually not written all that well. Since I also had some IIS knowledge, I was generally the main support person for these little web apps, especially if they were running against a SQL Server database.
There was one app, and I don’t even remember what the purpose was, that kicked the user out of the app if they closed a particular window. That wouldn’t be so bad, except that there was a flag in the user row that was now stuck at true that said the user was logged in and wouldn’t let them relaunch the app. There was no way to fix this in the admin screen. The only way to remedy the problem was to run a T-SQL statement to flip the flag back to false.
This was happening a lot and at least one or two calls a day were getting escalated to me. I knew I had to give the help desk a way to fix the issue, but I didn’t want them to actually have to write a T-SQL statement or even need to know how to open SSMS.
Probably the best way to fix this would be to create a nice custom ASP.Net page in C# that allowed the help desk to reset the flag whenever they got a call about the problem. Instead, I had in mind a solution that I could develop in just a few minutes.
SQL Server Reporting Services (SSRS) reports can run against a hard-coded query or a stored procedure. One stored procedure can both modify data and return a result set, and SSRS doesn't care. SSRS receives the data and it displays the data. SSRS will pass parameters to a stored procedure. Again, SSRS doesn't care if the parameters are used to filter data or to do something else.
So, my first step was to create a stored procedure that displayed a list of all the users logged into the app and flip the flag at the same time. As long as no valid @UserID was passed to it, all it did was return the data. It looked something like this:
CREATE PROC usp_ShowAndCorrectLoggedInUsers @UserID VARCHAR(10) = NULL AS
SET LoggedIn = 0
WHERE UserID = @UserID;
SELECT UserID, Name
WHERE LoggedIn = 1;
Then I created an SSRS report with this proc as the dataset that displayed the list of logged in users.
I made sure that the parameter was set to accept NULL values and that it was hidden.
I then linked each UserID back to the same report passing in the UserID as the parameter value.
I gave the help desk security group permission to run the stored procedure but no other rights in the database. I published the report and submitted the link to the Help Desk Knowledge Base. Once the fix was approved, now when a call came in for this problem, the help desk person who got the call could run the report, then just click the link for the particular user with the issue. Then the report would run again, this time fixing the flag and redisplaying the results.
In this case, it was so easy to create an SSRS report instead of an actual .Net web page and took me about 5 minutes to do. I took advantage of the fact that a stored procedure can do much more than just return data, and SSRS doesn’t care.