May 31, 2012 at 7:41 am
Hi,
I have prepared a disk space report, but now I want to make it Red Bold wherever %FreeSpace is less than 10%. I am using the below T-SQL. Can anyone tell how should I modify it?
SELECT * FROM SpaceDetail
DECLARE@DiskReport NVARCHAR(MAX)
SET @DiskReport =
N'<H1>Disk Space Report</H1>'+
N'<table border="1" cellspacing="0" style= "font-size: 10pt">'+
N'<tr><th>Server</th>'+
N'<th>Drive</th>'+
N'<th>Total Space(in GB)</th>'+
N'<th>Free Space(in GB)</th>'+
N'<th>% FreeSpace</th></tr>'+
CAST ( ( SELECT td = [Server] , '',
td = [Drive] , '',
td = [DiskSize] , '',
td = [FreeSpace] , '',
td = [Percentage] , ''
FROM SpaceDetail
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) )+
N'</table>' ;
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DB Mail'
,@subject = 'Disk Space Report'
,@recipients = 'abc@xyz.com'
,@body = @DiskReport
,@body_format = 'HTML' ;
May 31, 2012 at 7:56 am
The first suggestion I would make is to leave formatting to the front end. Put this in SSRS and let the formatting go crazy. I would certainly not recommend using t-sql as an html creation tool. If this is a web page or something the same applies. Let sql get you the data and then you can format it in the front end where it belongs.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 31, 2012 at 8:01 am
Sean Lange (5/31/2012)
The first suggestion I would make is to leave formatting to the front end. Put this in SSRS and let the formatting go crazy. I would certainly not recommend using t-sql as an html creation tool. If this is a web page or something the same applies. Let sql get you the data and then you can format it in the front end where it belongs.
Ditto on this. T-SQL is not made for making things pretty. That's what front end tools are for.
May 31, 2012 at 8:11 am
Thanks for your reply. But I need this in T-SQL, SSRS is not available.
May 31, 2012 at 8:13 am
Where are you going to display this? That is where you should format it.
If you are deadset on on forcing this square peg through t-sql you need to post ddl and sample data along with desired output.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 31, 2012 at 8:18 am
The T-SQL which I have given above is schuled as a job and report is generated in table format like this.
ServerDriveTotal Space(in GB)Free Space(in GB)% FreeSpace
Server1C:100.0 92 22.0
Server2D:70 55 30.3
May 31, 2012 at 8:22 am
first link i found when googling
http://jahaines.blogspot.co.uk/2010/04/t-sql-tuesday-005-creating-emailing.html
May 31, 2012 at 8:24 am
you simply want to use a case statement;
something like this:
---
N'<th>Total Space(in GB)</th>'
+ CASE
WHEN [FreeSpace] <= 90.0
THEN N'<th><span style="color:#FF0000";>Free Space(in GB)<//span></th>'+
ELSE N'<th>Free Space(in GB)</th>'+
END +
N'<th>Free Space(in GB)</th>'+
---
Lowell
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply