→ Celebrate SysAdmin Day 2024 with Lansweeper Enter our Giveaway here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
dglaw
Engaged Sweeper
I have been trying to create a report that will tell me all of the computers of a certain model (Optiplex GX280 and Optiplex GX520) that have less than 1G of RAM. Whenever I try to run the report, I get an access violation error and then I have to restart the report builder program. Here is the SQL script I used for the report:

SELECT
dbo.tblComputers.Computername AS Computer,
dbo.tblOperatingsystem.Description,
CAST(CAST(dbo.tblComputersystem.TotalPhysicalMemory AS bigint) / 1024 / 1024 AS NUMERIC) AS Memory,
dbo.tblComputers.Domain,
dbo.tblComputersystem.Model,
dbo.tblComputers.Lastseen
FROM
dbo.tblComputers
INNER JOIN dbo.tblOperatingsystem ON (dbo.tblComputers.Computername = dbo.tblOperatingsystem.Computername)
INNER JOIN dbo.tblComputersystem ON (dbo.tblComputers.Computername = dbo.tblComputersystem.Computername)
INNER JOIN dbo.tblComputersystem tblComputersystem_1 ON (dbo.tblComputers.Computername = tblComputersystem_1.Computername)
WHERE
CAST(CAST(dbo.tblComputersystem.TotalPhysicalMemory AS bigint) / 1024 / 1024 AS NUMERIC) < 1024 AND
dbo.tblComputersystem.Model BETWEEN 'Optiplex GX280' AND 'Optiplex GX520'
ORDER BY
dbo.tblComputers.Domain,
Memory,
dbo.tblComputersystem.Model

When I take out one of the lines under the Where statement ( it doesn't matter which one) the report runs perfectly. I am hoping there is a way to get both lines in and have the report work.

Thanks in advance for any and all responses.
2 REPLIES 2
Hemoco
Lansweeper Alumni
The report builder shouldn't crash (regardless of your error)
We'll see if we can fix this.
dglaw
Engaged Sweeper
I found the fix, I'll I guess I need to bone up on my sql scripting!

SELECT
dbo.tblComputers.Computername AS Computer,
dbo.tblOperatingsystem.Description,
CAST(CAST(dbo.tblComputersystem.TotalPhysicalMemory AS bigint) / 1024 / 1024 AS NUMERIC) AS Memory,
dbo.tblComputers.Domain,
dbo.tblComputersystem.Model,
dbo.tblComputers.Lastseen
FROM
dbo.tblComputers
INNER JOIN dbo.tblOperatingsystem ON (dbo.tblComputers.Computername = dbo.tblOperatingsystem.Computername)
INNER JOIN dbo.tblComputersystem ON (dbo.tblComputers.Computername = dbo.tblComputersystem.Computername)
INNER JOIN dbo.tblComputersystem tblComputersystem_1 ON (dbo.tblComputers.Computername = tblComputersystem_1.Computername)
WHERE
CAST(CAST(dbo.tblComputersystem.TotalPhysicalMemory AS bigint) / 1024 / 1024 AS NUMERIC) < 1024 AND
tblComputersystem_1.Model BETWEEN 'Optiplex GX280' AND 'Optiplex GX520'
ORDER BY
dbo.tblComputersystem.Computername

New to Lansweeper?

Try Lansweeper For Free

Experience Lansweeper with your own data.
Sign up now for a 14-day free trial.

Try Now