|
rlauder -> Unable to export results from complex query due null lines (10/2/2008 12:52:29 PM)
|
If anyone can help me with this, I'd be very pleased indeed!
I have a monster query that we use to show current disk and cpu info for our community of laptops. In order to show C & D drive info (and CPU1 & CPU2 info) as separate columns, I've had to resort to creating temporary tables to store the intermediate results.
If I run it in SQL Query Analyser it works a treat. It actually runs fine through the SMS web browser but the "INSERT INTO " entries cause blank, "no records found" lines to come up on the browser before the meaty report with all the laptop data in it.
As a result of these "null" lines, the export function doesn't work. I guess it looks at the first piece of data, sees nothing and decides that there's nothing to export.
Does anyone know how I can get the web report to run without displaying lines associated with the INSERT commands so that I can export the main report?
Here's the SQL ==>
CREATE TABLE #CDisk (resourceid varchar(64), size0 varchar(64), freespace0 varchar(64))
CREATE TABLE #DDisk (resourceid varchar(64), size0 varchar(64), freespace0 varchar(64))
CREATE TABLE #CPU1 (resourceid varchar(64), currentclockspeed0 int, maxclockspeed0 int)
CREATE TABLE #CPU2 (resourceid varchar(64), currentclockspeed0 int, maxclockspeed0 int)
INSERT INTO #CDisk
select distinct vrs.resourceid, dsk.size0, dsk.freespace0
from v_R_system vrs inner join v_gs_logical_disk dsk on vrs.resourceid = dsk.resourceid
where dsk.name0 = 'C:'
INSERT INTO #DDisk
select distinct vrs.resourceid, dsk.size0, dsk.freespace0
from v_R_system vrs inner join v_gs_logical_disk dsk on vrs.resourceid = dsk.resourceid
where dsk.name0 = 'D:'
INSERT INTO #CPU1
select vrs.resourceid, cpu.currentclockspeed0, cpu.maxclockspeed0
from v_R_system vrs inner join v_GS_Processor cpu on vrs.resourceid = cpu.resourceid
where cpu.deviceid0 = 'CPU0'
INSERT INTO #CPU2
select vrs.resourceid, cpu.currentclockspeed0, cpu.maxclockspeed0
from v_R_system vrs inner join v_GS_Processor cpu on vrs.resourceid = cpu.resourceid
where cpu.deviceid0 = 'CPU1'
select distinct VRS.Name0, ws.LastHWScan, VRS.User_Name0, CS.Model0,
cp1.currentclockspeed0 as "Current Speed CPU1/MHz", cp1.maxclockspeed0 as "Max Speed CPU1/MHz",
cp2.currentclockspeed0 as "Current Speed CPU2/MHz", cp2.maxclockspeed0 as "Max Speed CPU2/MHz",
mem.totalphysicalmemory0 as "Total RAM",
ds1.size0 as "C: Total/MB", ds1.freespace0 as "C: Free/MB",
ds2.size0 as "D: Total/MB", ds2.freespace0 as "D: Free/MB"
from v_R_System VRS INNER JOIN v_GS_COMPUTER_SYSTEM CS on vrs.resourceid = cs.resourceid
inner join v_GS_SYSTEM_ENCLOSURE senc on vrs.resourceid = senc.resourceid
inner join v_GS_WORKSTATION_STATUS ws on vrs.resourceid = ws.resourceid
left join #CPU1 cp1 on vrs.resourceid = cp1.resourceid
left join #CPU2 cp2 on vrs.resourceid = cp2.resourceid
inner join v_GS_X86_PC_Memory mem on vrs.resourceid = mem.resourceid
inner join #CDisk ds1 on vrs.resourceid = ds1.resourceid
left join #DDisk ds2 on vrs.resourceid = ds2.resourceid
where senc.ChassisTypes0 in (8,9,10,12,14,21)
order by VRS.Name0
drop table #CDisk
drop table #DDisk
drop table #CPU1
drop table #CPU2
|
|
|
|
