To add text into the cell you can do something like what I found here. http://msdn.microsoft.com/en-us/library/ms187928.aspx
select top 5
cast (HDD_FreeSpace_MB0 /1024 AS VARCHAR) + ' GB' as 'Free Space'
from v_GS_BDNA_BDNA_HARDWARE_DATA_1_30
Here's another example of what you are trying to do;
SELECT sys.Name0 AS [Comptuer Name],
hw.OS_Name0 AS OS,
hw.OS_ServicePack0 AS [Service Pack],
hw.GeneralAvailabilityDate0 AS [HW Availability Date],
hw.Manufacturer0 AS Manufacturer,
hw.Product0 AS Model,
hw.Model0 AS Submodel,
hw.ObsoleteDate0 AS [HW Obsolete Date],
ROUND (CONVERT (FLOAT, hw.HDD_TotalSpace_MB0) / 1024, 2) AS [Disk Space],
ROUND (CONVERT (FLOAT, hw.HDD_FreeSpace_MB0) / 1024, 2) AS [Free Space],
ROUND (ROUND(CONVERT (FLOAT ,hw.RAM_TOTALPHYMEMORY_KB0) / 1048576, 2) * 1024,
0) AS Memory,
hw.CPU_ClockSpeed0 AS [CPU Speed],
hw.NumberOfCores0 AS [Core Count],
SUBSTRING(SYS.User_Name0, CHARINDEX('\', SYS.User_Name0) + 1,
LEN(SYS.User_Name0)) AS [User Name],
TUSER.sn0 AS [Last Name],
TUSER.givenName0 AS [First Name],
TUSER.Mail0 AS [Email Address],
TUSER.department0 AS Department,
TUSER.l0 AS Facility
FROM dbo.v_R_System sys
LEFT OUTER JOIN v_GS_BDNA_BDNA_HARDWARE_DATA_1_30 hw ON
sys.SMS_Unique_Identifier0 = hw.SMSUniqueIdentifier0
LEFT OUTER JOIN v_R_User TUSER ON TUSER.User_Name0 = SUBSTRING(sys.User_Name0,
CHARINDEX('\', sys.User_Name0) + 1, LEN(sys.User_Name0))
WHERE (sys.Name0 =@machinename)
________________________________
John Marcum
Sr. Desktop Architect
Bradley Arant Boult Cummings LLP
________________________________
From: [email protected] [mailto:[email protected]] On
Behalf Of Dzikowski, Michael
Sent: Friday, January 03, 2014 2:28 PM
To: [email protected]
Subject: RE: [mssms] SQL query question - convery to GB or MB
Ah! Thank you. That got me closer.
So when I add this:
ROUND(MEM.TotalPhysicalMemory0 / 1024.00, 0) AS [Memory (MB)]
I get this:
[cid:[email protected]]
When I try to divide again:
ROUND((MEM.TotalPhysicalMemory0 / 1024.00) / 1024, 0) AS [Memory (MB)]
That got me here :)
[cid:[email protected]]
How can I get rid of those trailing zeros?
From: [email protected]<mailto:[email protected]>
[mailto:[email protected]] On Behalf Of Jim Boberg
Sent: Friday, January 03, 2014 3:12 PM
To: [email protected]<mailto:[email protected]>
Subject: RE: [mssms] SQL query question - convery to GB or MB
Mike,
You can use ROUND in your Select statement and do the math there. For example,
replace TotalPhysicalMemory0 as 'Total Physical Memory' with
ROUND(MEM.TotalPhysicalMemory0 / 1024.00, 0) AS [Memory (MB)]
Jim
________________________________
From: [email protected]<mailto:[email protected]>
To: [email protected]<mailto:[email protected]>
Subject: [mssms] SQL query question - convery to GB or MB
Date: Fri, 3 Jan 2014 19:56:23 +0000
How can I convert the results from this query to show in MB or GB?
select Netbios_Name0 as 'Server Name', Description0, InitialSize0 as ' Page
File Initial Size', MaximumSize0 as 'Page File Max Size', TotalPageFileSpace0
as 'Total Page File Space', TotalPhysicalMemory0 as 'Total Physical Memory'
From
v_R_System VRS
Join v_GS_PAGE_FILE_SETTING PFILE on PFILE.ResourceID = VRS.ResourceID
JOIN v_GS_X86_PC_MEMORY MEM on MEM.ResourceID = VRS.ResourceID
[cid:[email protected]]
I saw John Nelson has a post on this, but was wondering if there was a
different way:
http://myitforum.com/myitforumwp/2011/11/02/sql-function-convert-bytes-to-kb-mb-gb-tb-pb-or-eb-and-format-the-output-to-human-readable-format/
Mike D-
________________________________
Confidentiality Notice: This e-mail is from a law firm and may be protected by
the attorney-client or work product privileges. If you have received this
message in error, please notify the sender by replying to this e-mail and then
delete it from your computer.
________________________________
Confidentiality Notice: This e-mail is from a law firm and may be protected by
the attorney-client or work product privileges. If you have received this
message in error, please notify the sender by replying to this e-mail and then
delete it from your computer.
<<inline: image001.png>>
<<inline: image002.png>>
<<inline: image003.png>>
