I also tried to run it in SSMS and it looks like some of the column names are incorrect: SMS_R_System.operatingSystem0 should be SMS_R_System.Operating_System_Name_And0, etc. [cid:[email protected]]
From: [email protected] [mailto:[email protected]] On Behalf Of Murray, Mike Sent: Tuesday, March 24, 2015 11:23 AM To: [email protected] Subject: [mssms] Help with SQL for report I'm trying to use this to create a report. When I run the report, I get: * An error has occurred during report processing. (rsProcessingAborted) Query execution failed for dataset 'DataSet1'. (rsErrorExecutingCommand) Here's the SQL: select distinct SMS_R_System.ItemKey,SMS_G_System_PC_BIOS.SerialNumber00,v_GS_SYSTEM_ENCLOSURE_UNIQUE.AssetTag,SMS_R_System.Name0,SMS_R_System.Client_Version0,SMS_R_System.Distinguished_Name0,SMS_R_System.operatingSystem0,SMS_R_System.operatingSystemServicePac0,SMS_R_System.Last_Logon_Timestamp0,___System_WORKSTATION_STATUS3.LastHWScan,SMS_G_System_COMPUTER_SYSTEM.Manufacturer00,SMS_G_System_COMPUTER_SYSTEM.Model00,SMS_G_System_COMPUTER_SYSTEM.TotalPhysicalMemory00,SMS_G_System_OPERATING_SYSTEM.InstallDate00,SMS_G_System_OPERATING_SYSTEM.LastBootUpTime00,__ystem_SYSTEM_CONSOLE_USAGE1.TopConsoleUser00,SMS_R_System.User_Name0,SMS_R_System.User_Domain0,SMS_G_System_PC_BIOS.SMBIOSBIOSVersion00,___System_ENCRYPTABLE_VOLUME2.DriveLetter00,___System_ENCRYPTABLE_VOLUME2.ProtectionStatus00,SMS_G_System_OPERATING_SYSTEM.Description00,SMS_R_System.description0 from vSMS_R_System AS SMS_R_System LEFT OUTER JOIN System_System_OU_Name_ARR AS __mSystem_System_OU_Name_ARR0 ON SMS_R_System.ItemKey = __mSystem_System_OU_Name_ARR0.ItemKey LEFT OUTER JOIN Computer_System_DATA AS SMS_G_System_COMPUTER_SYSTEM ON SMS_G_System_COMPUTER_SYSTEM.MachineID = SMS_R_System.ItemKey LEFT OUTER JOIN Operating_System_DATA AS SMS_G_System_OPERATING_SYSTEM ON SMS_G_System_OPERATING_SYSTEM.MachineID = SMS_R_System.ItemKey LEFT OUTER JOIN PC_BIOS_DATA AS SMS_G_System_PC_BIOS ON SMS_G_System_PC_BIOS.MachineID = SMS_R_System.ItemKey LEFT OUTER JOIN SYSTEM_CONSOLE_USAGE_DATA AS __ystem_SYSTEM_CONSOLE_USAGE1 ON __ystem_SYSTEM_CONSOLE_USAGE1.MachineID = SMS_R_System.ItemKey LEFT OUTER JOIN ENCRYPTABLE_VOLUME_DATA AS ___System_ENCRYPTABLE_VOLUME2 ON ___System_ENCRYPTABLE_VOLUME2.MachineID = SMS_R_System.ItemKey LEFT OUTER JOIN WorkstationStatus_DATA AS ___System_WORKSTATION_STATUS3 ON ___System_WORKSTATION_STATUS3.MachineID = SMS_R_System.ItemKey where (SMS_R_System.Obsolete0 = N'0') order by name0 Best Regards, Mike Murray Desktop Management Coordinator - IT Support Services California State University, Chico 530.898.4357 [email protected]<mailto:[email protected]> ________________________________ This message is intended for the sole use of the addressee, and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If you are not the addressee you are hereby notified that you may not use, copy, disclose, or distribute to anyone the message or any information contained in the message. If you have received this message in error, please immediately advise the sender by reply email and delete this message.
