Do the queries work in SQL Server Management Studio, just not SQL Server Reporting Services?
Chad Simmons | Microsoft System Center Configuration Manager Consultant | linkedin.com/in/chadsimmons From: [email protected] [mailto:[email protected]] On Behalf Of Murray, Mike Sent: Friday, January 8, 2016 5:09 PM To: [email protected] Subject: [mssms] Report help - SQL Can anyone help me with this report I'm trying to build for one of our techs? Both ways I've tried bomb out saying: An error has occurred during report processing. (rsProcessingAborted) Query execution failed for dataset 'DataSet1'. (rsErrorExecutingCommand) For more information about this error navigate to the report server on the local server machine, or enable remote errors I've included the 2 different queries I've tried below. Thanks! Method 1 Select Distinct sys1.Netbios_Name0 AS [Computer Name], sys1.user_name0 AS [User Name], sys1.Resource_Domain_OR_Workgr0 AS [Domain/Workgroup], [Top Console User] = CASE when (tusr.TopConsoleUser0 is NULL or tusr.TopConsoleUser0 = '-1') then 'Unknown' Else tusr.TopConsoleUser0 End, os.Caption0 AS [OS Edition], os.CSDVersion0 AS [Service Pack], enc.SMBIOSAssetTag0 AS [Asset Tag], CONVERT(VARCHAR(26), bios.ReleaseDate0, 101) AS [BIOS Date], cs.Manufacturer0 AS [Manufacturer], cs.Model0 as [Model], cpu.NormSpeed0 as [CPU Speed], mem.TotalPhysicalMemory0 as [Total RAM], dsk.Size0 as [Disk Size], dsk.FreeSpace0 as [Free Space], chi64.Chico_AssetTag0 as [Reg Asset], chi64.Chico_BuildingCode0 as [Building], chi64.Chico_RoomCode0 as [Room], chi64.Chico_BusinessArea0 as [Bus Area], CONVERT(VARCHAR(26), chi64.OSD_Base0, 101) AS [OSD Base], CONVERT(VARCHAR(26), chi64.OSD_Deploy0, 101) AS [OSD Deploy], chi64.Role_Admin0 as [Role Admin], chi64.Roles0 as [Roles], nic.Description0 AS [NIC Description], nic.IPAddress0 AS [IP Address], nic.IPSubnet0 AS [Subnet Mask], nic.DefaultIPGateway0 AS [Gateway] from v_R_System_Valid sys1 LEFT JOIN v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP tusr on tusr.ResourceID = sys1.ResourceID INNER JOIN v_GS_OPERATING_SYSTEM os on os.ResourceID = sys1.ResourceID INNER JOIN v_GS_SYSTEM_ENCLOSURE_UNIQUE enc on enc.ResourceID = sys1.ResourceID INNER JOIN v_GS_PC_BIOS bios on bios.ResourceID = sys1.ResourceID INNER JOIN v_GS_COMPUTER_SYSTEM cs on cs.ResourceID = sys1.ResourceID INNER JOIN v_GS_X86_PC_MEMORY mem on mem.ResourceID = sys1.ResourceID INNER JOIN v_GS_PROCESSOR cpu on cpu.ResourceID = sys1.ResourceID LEFT JOIN V_GS_Chico640 chi64 on chi64.resourceID = sys1.ResourceID INNER JOIN v_FullCollectionMembership fcm on fcm.ResourceID = sys1.ResourceID INNER JOIN v_GS_LOGICAL_DISK dsk on dsk.ResourceID = sys1.ResourceID inner join v_GS_NETWORK_ADAPTER_CONFIGUR nic on nic.ResourceID = sys1.ResourceID Where fcm.CollectionID = @CollID and dsk.DeviceID0 = 'C:' order by sys1.Netbios_Name0 Method 2 select distinct v_R_System_Valid.ResourceID, v_R_System_Valid.Netbios_Name0 AS [Computer Name], v_R_System_Valid.user_name0 AS [User Name], v_R_System_Valid.Resource_Domain_OR_Workgr0 AS [Domain/Workgroup], v_Site.SiteName as [SMS Site Name], [Top Console User] = CASE when (v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 is NULL or v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 = '-1') then 'Unknown' Else v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 End, v_GS_OPERATING_SYSTEM.Caption0 AS [Operating System], v_GS_OPERATING_SYSTEM.CSDVersion0 AS [Service Pack Level], v_GS_SYSTEM_ENCLOSURE_UNIQUE.SerialNumber0 AS [Serial Number], v_GS_PC_BIOS.ReleaseDate0 AS [BIOS Release Date], v_GS_SYSTEM_ENCLOSURE_UNIQUE.SMBIOSAssetTag0 AS [Asset Tag], v_GS_COMPUTER_SYSTEM.Manufacturer0 AS [Manufacturer], v_GS_COMPUTER_SYSTEM.Model0 AS [Model], v_GS_X86_PC_MEMORY.TotalPhysicalMemory0 AS [Memory (KBytes)], v_GS_PROCESSOR.NormSpeed0 AS [Processor (GHz)], (Select sum(Size0) from v_GS_LOGICAL_DISK inner join v_FullCollectionMembership on (v_FullCollectionMembership.ResourceID = v_GS_LOGICAL_DISK.ResourceID ) where v_GS_LOGICAL_DISK.ResourceID =v_R_System_Valid.ResourceID and v_FullCollectionMembership.CollectionID = @CollectionID) As [Disk Space (MB)], (Select sum(v_GS_LOGICAL_DISK.FreeSpace0) from v_GS_LOGICAL_DISK inner join v_FullCollectionMembership on (v_FullCollectionMembership.ResourceID = v_GS_LOGICAL_DISK.ResourceID ) where v_GS_LOGICAL_DISK.ResourceID =v_R_System_Valid.ResourceID and v_FullCollectionMembership.CollectionID = @CollectionID) As [Free Disk Space (MB)] , v_GS_Chico640.Chico_AssetTag0 as [Reg Asset], v_GS_Chico640.Chico_BuildingCode0 as [Building], v_GS_Chico640.Chico_RoomCode0 as [Room], v_GS_Chico640.Chico_BusinessArea0 as [Bus Area], v_GS_Chico640.OSD_Base0 as [OSD Base], v_GS_Chico640.OSD_Deploy0 as [OSD Deploy], v_GS_Chico640.Role_Admin0 as [Role Admin], v_GS_Chico640.Roles0 as [Roles], [IP Address] = CASE when (v_GS_NETWORK_ADAPTER_CONFIGUR.IPAddress0 is NULL) then 'Unknown' Else v_GS_NETWORK_ADAPTER_CONFIGUR.IPAddress0 End v_GS_NETWORK_ADAPTER_CONFIGUR.Description0 AS [NIC Description], v_GS_NETWORK_ADAPTER_CONFIGUR.IPAddress0 AS [IP Address], v_GS_NETWORK_ADAPTER_CONFIGUR.IPSubnet0 AS [Subnet Mask], v_GS_NETWORK_ADAPTER_CONFIGUR.DefaultIPGateway0 AS [Gateway] v_GS_NETWORK_ADAPTER_CONFIGUR.DNSServerSearchOrder0 AS [DNS Servers], v_GS_NETWORK_ADAPTER_CONFIGUR.WINSPrimaryServer0 AS [Pri WINS], v_GS_NETWORK_ADAPTER_CONFIGUR.WINSSecondaryServer0 AS [Sec WINS]*/ from v_R_System_Valid inner join v_GS_OPERATING_SYSTEM on (v_GS_OPERATING_SYSTEM.ResourceID = v_R_System_Valid.ResourceID) left join v_GS_SYSTEM_ENCLOSURE_UNIQUE on (v_GS_SYSTEM_ENCLOSURE_UNIQUE.ResourceID = v_R_System_Valid.ResourceID) inner join v_GS_COMPUTER_SYSTEM on (v_GS_COMPUTER_SYSTEM.ResourceID = v_R_System_Valid.ResourceID) inner join v_GS_PC_BIOS on (v_GS_PC_BIOS.ResourceID = v_R_System_Valid.ResourceID) inner join v_GS_X86_PC_MEMORY on (v_GS_X86_PC_MEMORY.ResourceID = v_R_System_Valid.ResourceID) inner join v_GS_PROCESSOR on (v_GS_PROCESSOR.ResourceID = v_R_System_Valid.ResourceID) left join v_GS_Chico640 on (v_GS_Chico640.ResourceID = v_R_System_Valid.ResourceID) INNER JOIN v_GS_OPERATING_SYSTEM ON (v_GS_NETWORK_ADAPTER_CONFIGUR.ResourceID = net.v_GS_OPERATING_SYSTEM.ResourceID) inner join v_GS_NETWORK_ADAPTER_CONFIGUR on (v_GS_NETWORK_ADAPTER_CONFIGUR.ResourceID = v_R_System_Valid.ResourceID) inner join v_FullCollectionMembership on (v_FullCollectionMembership.ResourceID = v_R_System_Valid.ResourceID) left join v_Site on (v_FullCollectionMembership.SiteCode = v_Site.SiteCode) inner join v_GS_LOGICAL_DISK on (v_GS_LOGICAL_DISK.ResourceID = v_R_System_Valid.ResourceID) and v_GS_LOGICAL_DISK.DeviceID0=SUBSTRING(v_GS_OPERATING_SYSTEM.WindowsDirectory 0,1,2) left join v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP on (v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.ResourceID = v_R_System_Valid.ResourceID) Where v_FullCollectionMembership.CollectionID = @CollectionID Order by v_R_System_Valid.Netbios_Name0 Best Regards, Mike Murray Desktop Management Coordinator - IT Support Services California State University, Chico 530.898.4357 <mailto:[email protected]> [email protected]
