SQL Query Error

Question

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

Answer 1

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