| Author |
 Topic  |
|
|
mqh7
Yak Posting Veteran
58 Posts |
 Posted - 08/17/2012 : 12:25:59
|
I have 2 SQL staements. The first 8 lines in each are the same. The only differenc is the WHERE line.
SELECT DISTINCT
a.Name0 AS [Machine Name],
b.SiteCode,c.FileVersion AS [Internet Explorer],
d.Operating_System_Name_and0,
c.FilePath
FROM v_GS_SoftwareFile c INNER JOIN v_GS_SYSTEM a ON c.ResourceID = a.ResourceID
INNER JOIN v_R_System d ON a.ResourceID = d.ResourceID
INNER JOIN v_FullCollectionMembership b ON a.ResourceID = b.ResourceID
WHERE (c.FileName = 'rtvscan.exe') AND (c.FileVersion like '10.%' )
ORDER BY a.Name0
this finds 598 machines running rtvscan.exe version 10.x and almost all of them have a -MC- in the the NetBIOS name.
SELECT DISTINCT
a.Name0 AS [Machine Name],
b.SiteCode,c.FileVersion AS [Symantec],
d.Operating_System_Name_and0,
c.FilePath
FROM v_GS_SoftwareFile c INNER JOIN v_GS_SYSTEM a ON c.ResourceID = a.ResourceID
INNER JOIN v_R_System d ON a.ResourceID = d.ResourceID
INNER JOIN v_FullCollectionMembership b ON a.ResourceID = b.ResourceID
WHERE (c.FileName = @Exename) AND (c.FileVersion like @FileVersion ) AND (a.Name0 like @PCName)
ORDER BY a.Name0
This finds only 78 machines. The criteria I entered for the 3 prompts was this:
Enter the EXE name you want to find >> rtvscan.exe
Enter the file version you want to find >> %10.%
Enter the PC name you want to find >> %-MC-%
Why the huge difference in results? |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47099 Posts |
Posted - 08/17/2012 : 12:46:49
|
your initial statement says
almost all of them have a -MC- in the the NetBIOS name
so if there are records without -MC- pattern they will definitely get missed in second result. first check if thats the cause
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
mqh7
Yak Posting Veteran
58 Posts |
Posted - 08/17/2012 : 12:51:42
|
In the first result over 575 machines have the -MC- pattern. In the second report all 78 machines returned have the -MC- pattern.
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47099 Posts |
Posted - 08/17/2012 : 12:57:07
|
what are the datatypes of parameter through which you sent the parameter values?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
webfred
Flowing Fount of Yak Knowledge
Germany
8514 Posts |
Posted - 08/17/2012 : 13:06:00
|
Which application is prompting for the parameter values?
Too old to Rock'n'Roll too young to die. |
|
|
|
mqh7
Yak Posting Veteran
58 Posts |
Posted - 08/17/2012 : 13:06:36
|
| I don't understand your question. In the SCCM report tool you run the report and it throws up 3 prompts asking you for values. I enter rtvscan.exe, %10.% & %-MC-% for the 3 prompts and then it runs the report. So something is happening since it's not finding almost 500 machines. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47099 Posts |
Posted - 08/17/2012 : 13:13:06
|
quote:
Originally posted by mqh7
I don't understand your question. In the SCCM report tool you run the report and it throws up 3 prompts asking you for values. I enter rtvscan.exe, %10.% & %-MC-% for the 3 prompts and then it runs the report. So something is happening since it's not finding almost 500 machines.
I'm asking datatypes for @ExeName,@Fileversion and @PCName
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
mqh7
Yak Posting Veteran
58 Posts |
Posted - 08/17/2012 : 14:00:03
|
| I see. I don't know what conversion, if any, the SCCM report tool performs. Do I define each before the prompt is asked? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47099 Posts |
Posted - 08/17/2012 : 14:11:07
|
quote:
Originally posted by mqh7
I see. I don't know what conversion, if any, the SCCM report tool performs. Do I define each before the prompt is asked?
i'm not asking on SCCM conversion
I'm asking about datatype you've defined for parameters in SQL code
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
mqh7
Yak Posting Veteran
58 Posts |
Posted - 08/17/2012 : 15:42:26
|
here is the entire script
SELECT DISTINCT a.Name0 AS [Machine Name],
b.SiteCode,c.FileVersion AS [Symantec],
d.Operating_System_Name_and0,
c.FilePath
FROM v_GS_SoftwareFile c INNER JOIN
v_GS_SYSTEM a ON c.ResourceID = a.ResourceID INNER JOIN
v_R_System d ON a.ResourceID = d.ResourceID INNER JOIN
v_FullCollectionMembership b ON a.ResourceID = b.ResourceID
WHERE (c.FileName = @Exename) AND (c.FileVersion like @FileVersion ) AND (a.Name0 like @PCName)
ORDER BY a.Name0
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47099 Posts |
Posted - 08/17/2012 : 15:44:51
|
quote:
Originally posted by mqh7
here is the entire script
SELECT DISTINCT a.Name0 AS [Machine Name],
b.SiteCode,c.FileVersion AS [Symantec],
d.Operating_System_Name_and0,
c.FilePath
FROM v_GS_SoftwareFile c INNER JOIN
v_GS_SYSTEM a ON c.ResourceID = a.ResourceID INNER JOIN
v_R_System d ON a.ResourceID = d.ResourceID INNER JOIN
v_FullCollectionMembership b ON a.ResourceID = b.ResourceID
WHERE (c.FileName = @Exename) AND (c.FileVersion like @FileVersion ) AND (a.Name0 like @PCName)
ORDER BY a.Name0
are you telling that you'll be using parameters without declaring them?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
mqh7
Yak Posting Veteran
58 Posts |
Posted - 08/17/2012 : 16:42:36
|
"are you telling that you'll be using parameters without declaring them?" Yes. I didn't know I had too. Remember, I've been working with SQL for all of 2 weeks now and I've never taken a class.
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47099 Posts |
Posted - 08/17/2012 : 17:18:43
|
quote:
Originally posted by mqh7
"are you telling that you'll be using parameters without declaring them?" Yes. I didn't know I had too. Remember, I've been working with SQL for all of 2 weeks now and I've never taken a class.
i'm not sure how SCCM interpret parameter types but if you can check them. I feel like it may be because of regarding them as character type as against variable character
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
huge difference in results
