August 27, 2012 at 2:25 pm
I currently have a job with HTML, T-SQL and e-mail that runs great. It pulls data from a table every morning formats into HTML code and emails the results to me. But....I'd like to get a little fancy with it.
Currently it's a simple table with 3 columns. What I'd like to do is add a bit of color to it.
The code I have running now is:
DECLARE @tableHTML NVARCHAR(max)
SET @tableHTML =
N'<H3><U>TITLE GOES HERE.</H3><U>' +
N'<table border="1" cellspacing="5">' +
N'<tr><th><h3>Server</th><th>Run Date</th><th>Job Name</h3></th></tr>' +
CAST ( (SELECT
td = [ServerName], '',
td = [RunDate], '',
td = [JobName], ''
FROM [xxxxxx_yyyyyyy_zzzzzz].[dbo].[Job_Report]
WHERE [Archived] <> 'Archived'
ORDER BY ServerName
FOR XML PATH('tr'), TYPE ) AS NVARCHAR(max) ) +
N'</TABLE>';
I know to add color to this I will have to do something with the "td" syntax. But at this time I'm not sure. My goal is to make the table outline yellow for example & make the background behind the contents red. Any suggestions?
Thanks!!
August 27, 2012 at 2:28 pm
Google "HTML add color to a table"
http://www.htmlgoodies.com/tutorials/colors/article.php/3478971/So-You-Want-Color-in-a-Table-Huh.htm
Jared
CE - Microsoft
August 27, 2012 at 2:32 pm
John here's your example you posted, modified so every other row is manill folder/white;
if i have specific cells i want colored, i usually use a CSS class.
let me know if you need an example of that.
DECLARE @tableHTML NVARCHAR(max)
SET @tableHTML =
N'<H3><U>TITLE GOES HERE.</H3><U>' +
N'<table border="1" cellspacing="5">' +
N'<tr><th><h3>Server</th><th>Run Date</th><th>Job Name</h3></th></tr>' +
CAST ( (SELECT
Row_Number() Over(Order By [ServerName], RunDate) % 2 As [TRRow],
td = [ServerName], '',
td = [RunDate], '',
td = [JobName], ''
FROM [xxxxxx_yyyyyyy_zzzzzz].[dbo].[Job_Report]
WHERE [Archived] <> 'Archived'
ORDER BY ServerName
FOR XML PATH('tr'), TYPE ) AS NVARCHAR(max) ) +
N'</TABLE>';
--using REPLACE to change wird XML to valid HTML tags:
Set @tableHTML = Replace(@tableHTML, '_x0020_', space(1))
Set @tableHTML = Replace(@tableHTML, '_x003D_', '=')
Set @tableHTML = Replace(@tableHTML, '<tr><TRRow>1</TRRow>', '<tr bgcolor=#C6CFFF>')
Set @tableHTML = Replace(@tableHTML, '<TRRow>0</TRRow>', '')
edit: see this thread; very similar: http://www.sqlservercentral.com/Forums/Topic1348439-391-1.aspx#bm1348531
Lowell
August 27, 2012 at 2:38 pm
SQLKnowItAll (8/27/2012)
Google "HTML add color to a table"http://www.htmlgoodies.com/tutorials/colors/article.php/3478971/So-You-Want-Color-in-a-Table-Huh.htm
Ironically I had that very same page opened when I wrote the email. But it didn't say how to implement that code into a t-sql statement. That was straight html only, no t-sql implementation.
August 27, 2012 at 2:44 pm
John Waclawski (8/27/2012)
SQLKnowItAll (8/27/2012)
Google "HTML add color to a table"http://www.htmlgoodies.com/tutorials/colors/article.php/3478971/So-You-Want-Color-in-a-Table-Huh.htm
Ironically I had that very same page opened when I wrote the email. But it didn't say how to implement that code into a t-sql statement. That was straight html only, no t-sql implementation.
Yes, but the HTML is not rendered as SQL, it is rendered as HTML. The SQL is creating the HTML.
Jared
CE - Microsoft
August 27, 2012 at 2:50 pm
LOWELL!! MY SQL HERO!! Let me give this a shot & see how it works!
Thanks. BTW...did you get my reply from our other thread?
August 27, 2012 at 2:55 pm
John Waclawski (8/27/2012)
LOWELL!! MY SQL HERO!! Let me give this a shot & see how it works!Thanks. BTW...did you get my reply from our other thread?
for assigning classes to a TD, I use stuff like this:
SELECT @Body = = (SELECT
[ServerName] AS
,
[RunDate] AS
[JobName] AS
FROM SomeTable
For XML raw('tr'), Elements)
-- Replace the entity codes and row numbers
Set @Body = Replace(@Body, '_x0020_', space(1))
Set @Body = Replace(@Body, '_x003D_', '=')
Set @Body = Replace(@Body, '<tr><TRRow>1</TRRow>', '<tr bgcolor=#C6CFFF>')
Set @Body = Replace(@Body, '<TRRow>0</TRRow>', '')
Select @Body = @PageHead + @TableHead + @Body + @TableTail
where the string @TableHead has the CSS definition in the <head><style> tags.
the thread on the Triggers? yes I did; i was very glad to be able to help there!
Lowell
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply