Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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
share|improve this question

1 Answer 1

up vote 0 down vote accepted

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
share|improve this answer
    
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 '09 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. –  Joshua Nurczyk Jun 2 '09 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. –  Evan Anderson Jun 2 '09 at 21:09
    
sounds good, thanks for the help everyone –  ajp Jun 3 '09 at 0:46

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.