Creating SMS Web Reports from SMS Queries - Part V
Modifying the report to use views
In Part 4, we defined what SQL views are and compared them with the tables. Now that you have all this SQL knowledge in that big brain of yours, we can finally start converting our SQL query to use SQL views instead of tables.
Switching to Views
We will start with our original SQL query which you should still have pasted in Query Analyzer. Make sure that your SMS database is selected in the database drop-down field on the toolbar before we proceed so that Query Analyzer knows that the queries you are running are against your SMS database. Here’s the original query again:
select all SMS_R_System.ItemKey,SMS_R_System.Name0,SMS_R_System.User_Name0,__System_ADD_REMOVE_PROGRAMS0.DisplayName00 from System_DISC AS SMS_R_System INNER JOIN Add_Remove_Programs_DATA AS __System_ADD_REMOVE_PROGRAMS0 ON __System_ADD_REMOVE_PROGRAMS0.MachineID = SMS_R_System.ItemKey where __System_ADD_REMOVE_PROGRAMS0.DisplayName00 = "Windows Internet Explorer 7"
Replace double quotes with single quotes
The first thing we’re going to do is to run the query and see what it returns. Go to the Query menu and select Execute, or hit the F5 key. The results pane will appear with the following error:
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'Windows Internet Explorer 7'.
The problem is that SQL is expecting single quotes around the text string, ‘Windows Internet Explorer 7’ instead of double quotes. In order to use double quotes, you could use the SET QUOTED_IDENTIFIER keyword, but instead of messing around with that we’ll go ahead and give SQL what its expecting, which are single quotes.
This isn’t rocket science; it’s the same thing you’ve done in Microsoft Word or Excel or a bajillion other apps. Go to the Edit menu and select Replace. Enter a double quote in the Find field and a single quote in the Replace with field, and then click on the Replace All button. If you don’t feel like doing a Find/Replace, you can always just directly make the edit to the query.
You’ll notice that as soon as you hit that button, ‘Windows Internet Explorer 7’ will turn red in the query window. That means that Query Analyzer now recognizes that as a text string because of the single quotes. Your Query Analyzer window should now look like this:
Now that we made a change, let’s see what happens when you run the query:
OK, so we’re getting some results now, but if you tried to put this query into a web report you would still get an ‘Access denied’ error as we still need to convert it to use SQL views. This is because of the permissions context that we are running under in Query Analyzer, which has more rights than when you run a web report. Notice also that the first column is displaying ItemKey instead of the SMS Site code so we will eventually need to fix that as well, but first we will take care of the views.
Replacing the table name System_DISC
Let’s start swapping table names for views by looking at the FROM…AS line in the query, which currently looks like this:
FROM System_DISC AS SMS_R_System
We discussed this line back in Part 3 of this article series, where it was explained that this line is selecting our columns from a table named System_DISC and aliasing it as SMS_R_System. What we need to do at this point is to replace System_DISC with its corresponding view name, v_R_System. After performing the replace, the line should look like this:
FROM v_R_System AS SMS_R_System
So now we are telling Query Analyzer to select columns from the view named v_R_System and alias it as SMS_R_System.
Replace SMS_R_System with v_R_System
The next thing we’re going to do is to replace the alias name SMS_R_System. As before, so again: Edit -> Replace and populate the fields as shown below, then hit Replace All to replace the multiple instances of SMS_R_System in the query:
Just to make sure we’re on the same page, Query Analyzer now looks like this:
Since we’ve made this change, take a look at the FROM…AS line:
FROM v_R_System AS v_R_System
Who’s writing this query? Someone from the Department Of Redundancy Department? What it is now saying is to select columns from the view named v_R_System and alias it as v_R_System. The whole purpose of using an alias is to give a long table or view name a shorter or friendlier way to reference the name. Since we are now just aliasing the view for what its name is anyway, we are going to remove it. Using an alias is optional, so this won’t affect the functionality of the query. Delete “AS v_R_System” from the FROM line so that it now reads as follows:
FROM v_R_System
We are now ready to move on to the next table/view name swaps that we need to do for Add_Remove_Programs.
Adding the Views for Add_Remove_Programs
This is a lot like what we just did for v_R_System. In the INNER JOIN line, replace the table name Add_Remove_Programs_DATA with its corresponding view name v_GS_ADD_REMOVE_PROGRAMS. Next, replace the alias name __System_ADD_REMOVE_PROGRAMS0 (note that that is a double underscore before System) with the view name v_GS_ADD_REMOVE_PROGRAMS, and you‘ll end up with this:
Once again, we have a self-referencing alias in the INNER JOIN line:
INNER JOIN v_GS_ADD_REMOVE_PROGRAMS AS v_GS_ADD_REMOVE_PROGRAMS
Delete the alias, and then it becomes this:
INNER JOIN v_GS_ADD_REMOVE_PROGRAMS
Hopefully at this point you’re not completely lost, but if you are then here is what the query text should look like:
select all v_R_System.ItemKey, v_R_System.Name0, v_R_System.User_Name0,
v_GS_ADD_REMOVE_PROGRAMS.DisplayName00
from v_R_System
INNER JOIN v_GS_ADD_REMOVE_PROGRAMS
ON v_GS_ADD_REMOVE_PROGRAMS.MachineID = v_R_System.ItemKey
where v_GS_ADD_REMOVE_PROGRAMS.DisplayName00 = 'Windows Internet Explorer 7'
Let’s see how we’re faring so far; go ahead and run the query and see what it does.
Did you get a bunch of column name errors like I got? Excellent! Everything is working according to my plan! In Part 6 of this series, we will examine what caused these errors and how to clean them up.