Pulling from an sms database - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Pulling from an sms database

Author

Topic

jseber1982
Starting Member

6 Posts

Posted - 2007-11-02 : 14:56:57

I am using access to pull info from my sms database.

Here is what i need to do. Have a column of all machines on the left. I will also have multiple columns going down to the right of that that list software packages and their install status.

So yes,no,yes,no will be listed going down next to the machine name. I will have multiple software packages so there will be multiple columns.

Oh yea, this is for a classified network, so i cant exactly copy and paste.

here is an example of what i am tryin.

dbo_v_gs_add_remove_programs: IIF([Displayname0] Like "winzip*",yes,no")

My problem with this,,, is that instead of listing the machine as either yes or no..... it goes and checks every software package on the machine and puts a no next to the rest of them... So just for 1 machine, i have about 50 no and 1 yes.

Hope i am clear enough ehe.

thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-02 : 14:58:24

Do you have a question about SQL Server?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

jseber1982
Starting Member

6 Posts

Posted - 2007-11-02 : 15:01:16

on the main page it said that this is the forum to post about queries. I am pulling data from a sql database into a query usint sql programming

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-02 : 15:07:50

But your question is about the application. SQL Server does not put things on the left/right/middle or whatever, your application does that.

If you can provide more specific information about what the database problem is, then we can help you. But the information that you have posted so far is not about SQL Server.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

jseber1982
Starting Member

6 Posts

Posted - 2007-11-02 : 15:18:15

sorry, as far as where is is displayed, that does not matter here.

"My problem with this,,, is that instead of listing the machine as either yes or no..... it goes and checks every software package on the machine and puts a no next to the rest of them... So just for 1 machine, i have about 50 no and 1 yes."

I tried case and it seems to do the same thing. I cant figure out any commands to list it only 1 time. Currently the query looks at every single row on the clumn and if it is not the application, it lists it as no. If it is, it lists it as yes.

In the query output, i am trying to get it so that each unique row will only have 1 yes/no next to it.

yea i am sure i just completely said what i said before, maybe i am in the wrong forum. or i just am not understanding the logic on how to accomplish this

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-02 : 15:23:50

Please post your query, sample data of the problem, and what the query should return when it is fixed. Posting the table structure is also recommended so that we can get familiar with your setup.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

jseber1982
Starting Member

6 Posts

Posted - 2007-11-02 : 15:33:07

This is what i am using.
Select dbo_v_GS_computer_system.name0, iif([displayname0] like "winzip*","yes","no") as dbo_v_GS_add_remove_programs from dbo_v_gs_computer innner join dbo_v_gs_add_remove_programs on dbo_v_gs_computer_system.resourceid = dbo_v_gs_add_remove_programs.resourceid
where (((dbo_v_gs_computer_system.name0)="computername"));

currently when i run it, it looks like this:

computername No
computername No
computername No
computername No
computername No
computername No
computername No
computername No
computername yes
computername No
computername No
computername No

and so on. Thats because it checks every row in the column against "winzip*" and since there is only 1 winzip, it reports the rest as no.

I want it to come up as

computername No

or

computername Yes

If the query finds winzip, just report it as a yes and do not display all of the others. And if it does not find winzip, just display it as a no 1 time.

Once i get this finished, i will change the computername to * to search all machines on the domain and i will add in more columns for more applications.

hopefully this info is a little bit better.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-02 : 15:35:48

Sounds like you just need to use DISTINCT or GROUP BY.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

jseber1982
Starting Member

6 Posts

Posted - 2007-11-02 : 15:42:38

Wouldnt those just look at the database itself? they all have different names, it is just the output in the query that is the same

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-02 : 15:45:17

If they all have different names, then please post better sample data. Your sample data has it all the same. We can't see your environment and most of us have never touched SMS, so you are going to need to be very explicit in your posts in order for us to help.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-02 : 16:10:52

try using a left join and adding the like criteria then grouping...

Select dbo_v_GS_computer_system.name0, iif([displayname0] like "winzip*","yes","no") as dbo_v_GS_add_remove_programs
from dbo_v_gs_computer innner
left join dbo_v_gs_add_remove_programs
on dbo_v_gs_computer_system.resourceid = dbo_v_gs_add_remove_programs.resourceid
and dbo_v_gs_add_remove_programs.[displayname0] like "winzip*"
where (((dbo_v_gs_computer_system.name0)="computername"));
group by dbo_v_GS_computer_system.name0, iif([displayname0] like "winzip*","yes","no")

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-03 : 06:58:26

IIF is a MS Access function.
I hope moderator moves this topic to the ACCESS forum.

E 12°55'05.25"
N 56°04'39.16"

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-11-03 : 20:36:33

Is sms Microsoft system management server? Should use sql db if so.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-03 : 21:21:58

It isn't clear to me that this is an Access query. I realize it's not pure T-SQL, but perhaps through sms you are able to add code to the query.

jseber1982, please let us know exactly what database system you are using.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

jseber1982
Starting Member

6 Posts

Posted - 2007-11-05 : 13:29:49

Currently i am doing the coding in access and doing a pull from the sql 2000 sms database. I figured that since i am doing the report in access, that i would be able to make it look better than just using the built in sms report tool.

I suppose that i can just cut access out of the loop and use strict sql from the query analyzer

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-05 : 13:31:39

Let us know what query you will be using in Query Analyzer. And please post better sample data so that we may help you. You indicated that the computernames were all different, yet your sample data doesn't show that. Show us good sample data that illustrates your problem.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources