|
jnelson993 -> RE: How do I convert WQL to SQL (1/27/2008 1:32:21 AM)
|
quote:
ORIGINAL: kev147
Can someone point me in the right direction with this please?
This is what I have currently:
--Main SQL Code:
select distinct sys.Netbios_Name0,
sysou.System_OU_Name0,
sys.Operating_System_Name_and0,
sys.User_Name0
from dbo.v_r_system_valid AS sys,
dbo.v_RA_System_SystemOUName AS sysou
where (sysou.System_OU_Name0 LIKE @OU)
**********************************
When I run the above report from the sccm admin console, I get this error:
Microsoft VBScript runtime error '800a0007'
Out of memory: 'GetRows'
/SMSReporting_1PR/Report.asp, line 546
**************************************
I have the code working as an SCCM query (WQL), I just wanted to convert it to a report (SQL), this code is here:
I'm sorry, I don't know how I missed this post, I certainly would have responded right away if I would have seen it.
Anyway, you have a cartesian/cross join (every row in the first table/view joins to every row in the second table/view) This could create billions of rows of output and overwhelm your IIS server and give you the out of memory error. SQL Management Studio can handle that kind of output which is why it works there for you, but IIS can't handle that bulk out of the box.
quote:
from dbo.v_r_system_valid AS sys,
dbo.v_RA_System_SystemOUName AS sysou
where (sysou.System_OU_Name0 LIKE @OU)
See, you're selecting everything from SYS and everything from SYSOU, but you've got no JOIN statement. If the two views have 10,000 rows, the cartesian/cross join would produce 100Million rows. And it goes up exponentially the more rows you have in each table.
So, that little bit should be rewritten like this:
from dbo.v_r_system_valid AS sys
INNER JOIN dbo.v_RA_System_SystemOUName AS sysou
ON sys.ResourceID = sysou.ResourceID
where (sysou.System_OU_Name0 LIKE @OU)
Although, there's one more thing missing, for every machine that's in an OU, it's a member of every link up the chain. So if a machine is assigned to the OU:
CORP.DOMAIN\TOP\LEVEL1\LEVEL2\LEVEL3
it will also have an OU record for
CORP.COMAIN\TOP\LEVEL1\LEVEL2
CORP.COMAIN\TOP\LEVEL1
CORP.COMAIN\TOP
CORP.COMAIN
It'll return 5 records for that one machine. So you'll probably want to use a GROUP BY and a MAX in your SQL in order to get the full OU for a machine and skip all of those intermediate levels. Sooo, I'd rewrite your whole query like this:
SELECT sys.Netbios_Name0 as [Computer Name],
MAX(sysou.System_OU_Name0) as [Full OU],
sys.Operating_System_Name_and0 as OS,
sys.User_Name0 as [User Name]
FROM dbo.v_r_system_valid AS sys
INNER JOIN dbo.v_RA_System_SystemOUName AS sysou
ON sys.ResourceID = sysou.ResourceID
WHERE sysou.System_OU_Name0 LIKE @OU
GROUP BY sys.Netbios_Name0,
sys.Operating_System_Name_and0,
sys.User_Name0
I don't have SCCM operating yet so I haven't run this myself, but I've done enough of these by hand and it's close enough to SMS so I'm pretty confident this is good code.
SOME THINGS TO NOTE ABOUT THIS QUERY: there's an INNER JOIN statement that your query was missing and the GROUP BY removes duplicates at the same time it aggregates the rows together so the DISTINCT isn't needed.
I hope this helps.
|
|
|
|
