1

Trying to perform a query on a group of network servers, but getting an odd syntax error. The top query will run successfully, but as soon as the Add/Remove Progs is added, "The multi-part identifier "SYS.ResourceID" could not be bound." In the end I want to be able to query a group of computers and view the Add/Remove Program list from each one.

Working Code to pull server type, service pack, etc:

SELECT DISTINCT SYS.Name, OPSYS.Caption0, OPSYS.CSDVersion0, OPSYS.InstallDate0,OPSYS.LastBootUpTime0
FROM v_FullCollectionMembership SYS
JOIN v_GS_OPERATING_SYSTEM OPSYS on SYS.ResourceID=OPSYS.ResourceID
WHERE SYS.CollectionID = @CollID
ORDER BY SYS.Name,OPSYS.Caption0

Not working:

SELECT DISTINCT SYS.Name, OPSYS.Caption0, OPSYS.CSDVersion0, OPSYS.InstallDate0,OPSYS.LastBootUpTime0, ARP.DisplayName0, ARP.Version0
FROM v_FullCollectionMembership SYS ,  v_Add_Remove_Programs ARP
JOIN v_GS_OPERATING_SYSTEM OPSYS on SYS.ResourceID=OPSYS.ResourceID
WHERE SYS.CollectionID = @CollID
ORDER BY SYS.Name,OPSYS.Caption0

1 Answer 1

0

SMS 2003, eh?

I don't see where you're joining against the v_Add_Remove_Programs (as ARP) table. You're going to have to name it in a join.

Totally off the cuff, this should work (but probably won't... smile I don't have this schema sitting around to query):

SELECT DISTINCT SYS.Name, OPSYS.Caption0, OPSYS.CSDVersion0, OPSYS.InstallDate0,OPSYS.LastBootUpTime0, ARP.DisplayName0, ARP.Version0
FROM v_FullCollectionMembership SYS
INNER JOIN v_GS_OPERATING_SYSTEM OPSYS on SYS.ResourceID = OPSYS.ResourceID
INNER JOIN v_Add_Remove_Programs ARP on SYS.ResourceID = ARP.ResourceID
WHERE SYS.CollectionID = @CollID
ORDER BY SYS.Name,OPSYS.Caption0
3
  • that works great - now is there a way to hide redundant data (such as multiple Name/CSD Version/etc from the query? As in have 1 server name, then the installed programs....and then the next server name and its programs...
    – ajp
    Jun 2, 2009 at 19:57
  • as with Evan, I don't know the schema, the "ORDER BY SYS.Name" should list each system in order, but I know SQL doesn't always follow ORDER BY directives. If not, your best bet may be to put the data in Excel or OpenOffice and manually sort. Jun 2, 2009 at 20:51
  • You can "hide" the "redundant" data by using a reporting engine (Crystal, Access, Datavision, etc) to process the output of the query. You're essentially talking about having a report a detail section grouped by some criteria. You're not going to get that out of an SQL query. Jun 2, 2009 at 21:09

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .