Selecting the Proper Column Names From the Views
In Part 5, we replaced the table names with view names, and tidied up the query by removing the aliases. When we ran the query, there were several errors regarding invalid column names:
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'MachineID'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'ItemKey'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'ItemKey'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'DisplayName00'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'DisplayName00'.
The reason that we are getting these errors is because the columns names in the views are different than those in the tables. Let’s take a look at the columns within the views and you’ll get a better idea.
Fixing the Error for MachineID
The first error in our list was for MachineID from the INNER JOIN line so we’ll check that one out first. In Query Analyzer, expand your SMS database name in the Object Browser on the left side, and then expand Views. Next, expand v_GS_ADD_REMOVE_PROGRAMS, and then expand its columns node. Notice that MachineID is not one of the listed columns, so when the query tried to reference v_GS_ADD_REMOVE_PROGRAMS.MachineID it was unable to find the MachineID column in that view.
So here’s how to fix it. Right-click on v_GS_ADD_REMOVE_PROGRAMS and select Edit to display the query that created the view. The following query will appear in the query pane:
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
ALTER VIEW dbo.v_GS_ADD_REMOVE_PROGRAMS AS SELECT MachineID As 'ResourceID', InstanceKey As 'GroupID', RevisionID, AgentID, TimeKey As 'TimeStamp',DisplayName00 As 'DisplayName0',InstallDate00 As 'InstallDate0',ProdID00 As 'ProdID0',Publisher00 As 'Publisher0',Version00 As 'Version0' FROM Add_Remove_Programs_DATA
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
The part I want to draw your attention to is the part I highlighted in the query, SELECT MachineID As 'ResourceID'. As you may recall from Part 4 of this article series, views are created by querying tables with a SQL statement and selecting the table columns that are used in the view. What’s happening here is that the view’s SQL query is selecting the MachineID column from the Add_Remove_Programs_DATA table and aliasing it as ResourceID in the view. If you look back over at the columns listed for the view, you will see ResourceID as one of the column names. So in order to get our query to work, we will have to replace MachineID with ResourceID since that is the column that is in the view. Close the child window by clicking on the gray X in the top right-hand corner of Query Analyzer, choose not to save the query, and you will be back at the original query. Look at the INNER JOIN line and find the column name, MachineID. I have it highlighted below:
INNER JOIN v_GS_ADD_REMOVE_PROGRAMS
ON v_GS_ADD_REMOVE_PROGRAMS.MachineID = v_R_System.ItemKey
Now replace MachineID with ResourceID and run the query again. The error for MachineID is now gone, and we just have the four other errors to deal with:
Fixing the Error for ItemKey
The next error we need to correct is ItemKey, which is used in both the SELECT and INNER JOIN lines. If you edit the v_R_System view you will see this line in its query:
ALTER VIEW dbo.v_R_System AS SELECT ItemKey As 'ResourceID', …
How convenient; ItemKey is also aliased as ResourceID. This is because both ItemKey and MachineID contain the same data, which is a unique serial number that is given to each SMS client. In some tables it is put in a column named ItemKey and in others it is in a column named ResourceID. The really cool thing is that if you look at the columns for most of the views, a lot of them have ResourceID as a column. This was done intentionally by the SMS Developers so that you could use ResourceID to link different views with a join.
So logically, we need to replace ItemKey with ResourceID in our query to reference the correct column name. Once we do that and run the query, we’ll be down to the last two errors that we need to fix:
Fixing the Error for DisplayName00
This last error that we need to resolve is referenced twice in both the SELECT and WHERE lines, and it is actually very simple to fix. Expand the columns for the view v_GS_ADD_REMOVE_PROGRAMS and the change that we need to make is fairly clear:
So instead of DisplayName00 we need DisplayName0. Once the two changes have been done, our SQL query should now look like this:
select all v_R_System.ResourceID, 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
where v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 = 'Windows Internet Explorer 7'
When we run the query, it is finally presenting us with some results:
You’re probably thinking that we’re home free and we can go ahead and copy our SQL query into a web report, but like so many other articles in this series, this one too ends in tragedy. Look at the first column. We’re getting a list of Resource IDs, but that not what we wanted when we started this whole process. Our original goal was to write an SMS web report to return the SMS Site Code, System Name, Last User Logon Name, and Add/Remove Program Display Name for any systems that have Internet Explorer 7 installed. To accomplish this, we’re going to need to get rid of ResourceID in the SELECT line and have it return the SMS Site Code instead, which is what we will be discussing in Part 7 of this article series.