There are only two columns listed for this view; we will be using SMS_Assigned_Sites0 for the SELECT line and ResourceID to join this to our other views.
To add this view.column name to our SELECT statement, we are going to use the drag-and-drop feature of Query Analyzer. Click on the view name dbo.v_RA_System_SMSAssignedSites and drag and drop it in between SELECT ALL and v_R_System.Name0. You will then need to type a period to distinguish it from the column name, and then drag and drop the column name SMS_Assigned_Sites0 after the period. Next, type a comma and a space to separate it from v_R_System.Name0. Finally, delete the dbo. from the beginning of the view name as its not necessary to keep it in there. Your SELECT line should now look like this:
select all v_RA_System_SMSAssignedSites.SMS_Assigned_Sites0, v_R_System.Name0, v_R_System.User_Name0, v_GS_ADD_REMOVE_PROGRAMS.DisplayName0
When we run this query later in the exercise, it will display the columns in the same order that they appear in the SELECT line which will be SMS site code, system name, user name, and Add/Remove Programs display name. If you wanted the columns in a different order, all you would need to do is to rearrange them in the SELECT line.
Adding an INNER JOIN
Are you ready for another error to deal with? Go ahead and run the query and you will see this message:
Server: Msg 107, Level 16, State 3, Line 1
The column prefix 'v_RA_System_SMSAssignedSites' does not match with a table name or alias name used in the query.
We’re getting this error because SQL doesn’t realize that this is a view because it’s not referenced in the FROM line. What we need to do is use an INNER JOIN to link it to v_R_System using two similar columns in each view, which happens to be the ResourceID column.
So now we’re going to learn how to write an INNER JOIN. The syntax for this statement is as follows:
INNER JOIN <view name to be joined> ON <view name to be joined.column name> = <view that is being joined to.column name>
In our case, <view name to be joined> is going to be v_RA_System_SMSAssignedSites, and <view that is being joined to> is v_R_System. The column names will both be ResourceID, as both views have this column. Now we’ll plug this info into our query, right after the existing INNER JOIN line. The new line should look like this:
INNER JOIN v_RA_System_SMSAssignedSites ON v_RA_System_SMSAssignedSites.ResourceID = v_R_System.ResourceID
…and the entire query should read as such:
select all v_RA_System_SMSAssignedSites.SMS_Assigned_Sites0, v_R_System.Name0, v_R_System.User_Name0, v_GS_ADD_REMOVE_PROGRAMS.DisplayName0
from v_R_System
INNER JOIN v_GS_ADD_REMOVE_PROGRAMS
ON v_GS_ADD_REMOVE_PROGRAMS.ResourceID = v_R_System.ResourceID
INNER JOIN v_RA_System_SMSAssignedSites
ON v_RA_System_SMSAssignedSites.ResourceID = v_R_System.ResourceID
where v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 = 'Windows Internet Explorer 7'
So do you feel lucky? Well, do ya, punk? Let’s try running the query again:
Congratulations, you’ve successfully converted an SMS query to a SQL query! At this point, you could paste this query into an SMS web report and it will work, but there is one last thing you can do to make the report a little more readable for your users.
Aliasing the Column Names
If you look at the column names in the query results, they have underscores and zeroes in the labels. With very little effort, we can use alias names to make the column names more user friendly. Let’s take a look at our existing SELECT statement:
select all v_RA_System_SMSAssignedSites.SMS_Assigned_Sites0, v_R_System.Name0, v_R_System.User_Name0, v_GS_ADD_REMOVE_PROGRAMS.DisplayName0
To add an alias, all we need to do is to add the SQL keyword AS after each column name and then enclose the alias name in single quotes. Change your SELECT line to read as follows:
select all v_RA_System_SMSAssignedSites.SMS_Assigned_Sites0 AS 'SMS Site',
v_R_System.Name0 AS 'System Name',
v_R_System.User_Name0 AS 'User Name',
v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 AS 'Add/Remove Programs Display Name'
from v_R_System
INNER JOIN v_GS_ADD_REMOVE_PROGRAMS
ON v_GS_ADD_REMOVE_PROGRAMS.ResourceID = v_R_System.ResourceID
INNER JOIN v_RA_System_SMSAssignedSites
ON v_RA_System_SMSAssignedSites.ResourceID = v_R_System.ResourceID
where v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 = 'Windows Internet Explorer 7'
Now when you run the query, the column names reflect what you put in for each alias:
All that remains now is to copy the SQL query into the SMS web report named “All Systems with Microsoft Internet Explorer 7” that we originally created back in Part 1. Highlight your SQL query text and copy it, then open the SMS Administrator Console. Expand the Reporting node, and then click on Reports. Find the report that we created and double-click on it to edit its properties. Click on the Edit SQL Statement button and delete the previous query, paste in our new and improved SQL query, and then click on OK twice. Now run the report by right-clicking on it, and then from the pop-up menu select All Tasks -> Run -> YourSMSReportingServerName. Your report should now be working and displaying rows and columns instead of rude error messages. In the screen shot below, please note that the ugly black boxes were placed over the report columns to conceal user and system names.
So that’s it; you now know how to convert an SMS query into a SQL query for a web report! Thanks for taking the time to read this series of articles. I hope you’ve learned a few things that you can use in your environment and I wish you success in your querigraphing!! That would be the practice or art of writing queries, and yes, I just made that word up. But remember, you heard it here first.
Many kind regards,
Brian Leary
SMS Administrator and Querigraphist
For Further Study…
If you want to learn more about creating SMS Web Reports or SQL, you can visit these links:
Using SMS 2003 SQL Views to Create Custom Reports
SQLCourse.com
W3Schools SQL Tutorial