huge difference in results - 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

General SQL Server Forums

New to SQL Server Administration

huge difference in results

Author

Topic

mqh7
Yak Posting Veteran

58 Posts

Posted - 2012-08-17 : 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

52326 Posts

Posted - 2012-08-17 : 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 - 2012-08-17 : 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

52326 Posts

Posted - 2012-08-17 : 12:57:07

what are the datatypes of parameter through which you sent the parameter values?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-08-17 : 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 - 2012-08-17 : 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

52326 Posts

Posted - 2012-08-17 : 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 - 2012-08-17 : 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

52326 Posts

Posted - 2012-08-17 : 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 - 2012-08-17 : 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

52326 Posts

Posted - 2012-08-17 : 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 - 2012-08-17 : 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

52326 Posts

Posted - 2012-08-17 : 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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources