Hi All, I'm tying to create a collection based on the following query: select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.IPSubnets != "10.10.1.0" Basically, the collection must contain all system except the ones residing in subnet 10.10.1.0. The above query works well except for the systems that contain more then one subnet. That is, some workstations in subnet 10.10.1.0 also have addtional subnets (in the properties of the systems, they'll have Subnet [0] = 10.10.0.1, Subnet [1] = 10.20.15.0 ...etc) . This causes these systems to appear in my collection. I cannot add the other subnets in my query because it will exclude other systems that must be in this collection. Not being an expert in SQL, does someone have an idea on how exclude the systems with more then one subnet from my collection?Thanks, Jestmat.

John, That worked perfectly (using subnet)! May I followup with another question: I would like the query to exclude several subnets. I tried: ... not in (select SMS_R_SYSTEM.ResourceID from SMS_R_System where SMS_R_System.IPSubnets = "10.10.1.0";"10.10.2.0") but it doesn't work. The logic is I need to exclude systems that are in 10.10.1.0 OR 10.10.2.0. I'm assuming my syntax is not correct. Thanks, Jesmat.

I've never done it the way Sherry mentioned. Of course she knows more about SQL than I do. ;-) Try doing where sms_r_system.ipsubnets = "10.10.1.0" or sms_r_system.ipsubnets = "10.10.2.0" or sms_r_system.ipsubnets = "10.10.3.0" John Marcum | http://myitforum.com/cs2/blogs/jmarcum |

Ok. It tuns out that both versions work but here is why I am getting the error: Queries that do work: (John) select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.ResourceId not in (select SMS_R_SYSTEM.ResourceID from SMS_R_System where SMS_R_System.IPSubnets = "10.1.52.0" or SMS_R_System.IPSubnets = "10.1.33.0" or SMS_R_System.IPSubnets = "10.7.1.0" or SMS_R_System.IPSubnets = "10.1.5.0") (Sherry) select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.ResourceId not in (select SMS_R_SYSTEM.ResourceID from SMS_R_System where SMS_R_System.IPSubnets in ("10.10.1.0","10.10.2.0","10.10.3.0")) Queries that do not work: (error: The query statement you entered is not valid. Please enter a valid query statement) (John) select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.ResourceId not in (select SMS_R_SYSTEM.ResourceID from SMS_R_System where SMS_R_SYSTEM.IPSubnets = "10.1.52.0" or SMS_R_SYSTEM.IPSubnets = "10.1.33.0" or SMS_R_SYSTEM.IPSubnets = "10.7.1.0" or SMS_R_System.IPSubnets = "10.1.5.0") (Sherry) select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.ResourceId not in (select SMS_R_SYSTEM.ResourceID from SMS_R_System where SMS_R_SYSTEM.IPSubnets in ("10.10.1.0","10.10.2.0","10.10.3.0")) Yes, that's right, the only difference between the ones that work and don't work it that the ones that don't work have "SMS_R_SYSTEM" written in caps (see text in bold). I've tested this this several times to make sure I wasn't going crazy. Are SQL statements case sensitive? If so, end of discussion. If not, does someone know why this is happening (or do I just go balistic)? Thanks, Jesmat.

Ok, any thoughts as to why I'm getting the error when the query is in caps? Jesmat.

Just a complete shot in the dark: is you SQL collation set to case sensitive?

The SQL server was installed with default values (which I beleive is case insensitive by default). Also, I did some SQL queries in the SQL Server Management Studio using filters that had a mix of small and large caps. All queries returned the correct values without errors. Jesmat.

I'm closing this thread since my intial request was to validate the logic of the qeury (which has been answered). As for the case sensitivity, I'll keep looking and let you know if I find anything. Thanks, Jesmat