InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 10:41:26 GMT20http://www.sqlservercentral.com/Forums/Topic1385334-1292-1.aspxAlan, Sorry for delay, but better later than never. Thank you. That is what I was looking for.EugeneWed, 05 Dec 2012 14:30:41 GMTrightontargethttp://www.sqlservercentral.com/Forums/Topic1385334-1292-1.aspxI don't have a specific example to show you, but another approach is to use the SELECT command using the FOR XML switch. Then use XSLT transformations to convert to the target format. ;-)Sun, 02 Dec 2012 23:41:51 GMTBrandon Foresthttp://www.sqlservercentral.com/Forums/Topic1385334-1292-1.aspx[quote][b]eugene.pipko (11/27/2012)[/b][hr]Thanks, what if I need to change font-weight at <tr> level?How do I access <tr>?[/quote]The top row (the first instance of the TR tag) you could add any attributes just like you would by writing the HTML like so:[code="xml"]N'<tr style=''font-family: arial black;''><th>InsertDate</th><th>WebServerName</th><th>Location</th><th>Total Records</th><th>Failed Records</th>' +[/code]There are a number of other ways... Let's say you wanted to access all the rows (<tr>'s), you could add a style sheet in the static HTML portion as follows: [code="sql"]SET @tableHTML = N'<style> tr {font-family: Segoe Script;}</style>' + N'<H3>Daily Failure Report</H3>' +[/code]What is going on here is a mix of HTML and CSS; again, W3schools.com is the place to get a grasp of HTML, CSS, XML, etc.. Below is a sample that you can use as a reference. Notice how I change the first row (the 1st <tr>), all the rows (all <tr>'s) and all the cells (the <td>'s)This:[code="sql"]IF (OBJECT_ID('tempdb..#tmpDailyReport') IS NOT NULL)DROP TABLE #tmpDailyReportCREATE TABLE #tmpDailyReport( Date varchar( 12 ) Null, WebServerName varchar( 150 ) NOT Null, Location varchar( 150 ) NOT Null, cnt_RecsTotal int Null, cnt_RecsFailed int Null)INSERT INTO #tmpDailyReport( Date, WebServerName, Location, cnt_RecsTotal,cnt_RecsFailed)select '2008-01-01','Amsterdam-Server','Amsterdam',1000,2unionselect '2008-01-01','London-Server','London Bridge',50,0unionselect '2008-01-01','London-Server','London West Minster',80,0unionselect '2008-01-01','Portugal-Server','Lisbon',5,1--SELECT * FROM #tmpDailyReportDECLARE @tableHTML NVARCHAR(MAX) ;SET @tableHTML = N'<style> tr {font-family: Segoe Script;}</style>' + N'<H3>Daily Failure Report</H3>' + N'<div><H5>Execution Date: <font color="blue">' + CONVERT(NVARCHAR(30), GETDATE(), 113) + '</FONT></H5></div>' + N'<table border="1">' + N'<tr style=''font-family: arial black;''><th>InsertDate</th><th>WebServerName</th><th>Location</th><th>Total Records</th><th>Failed Records</th>' + CAST ( ( SELECT td = Date, '', td = WebServerName, '', td = Location, '', td = cnt_RecsTotal, '', 'td/@style' = CASE WHEN cnt_RecsFailed > 0 THEN 'font-weight: bold; color: pink;' END, td = cnt_RecsFailed FROM #tmpDailyReport for xml path('tr'), TYPE) AS NVARCHAR(MAX) ) + N'</table>' + N'';SELECT @tableHTMLdrop table #tmpDailyReport [/code]Will produce this: [code="xml"]<style> tr {font-family: Segoe Script;}</style><H3>Daily Failure Report</H3><div><H5>Execution Date: <font color="blue">27 Nov 2012 14:30:21:433</FONT></H5></div><table border="1"><tr style='font-family: arial black;'><th>InsertDate</th><th>WebServerName</th><th>Location</th><th>Total Records</th><th>Failed Records</th><tr><td>2008-01-01</td><td>Amsterdam-Server</td><td>Amsterdam</td><td>1000</td><td style="font-weight: bold; color: pink;">2</td></tr><tr><td>2008-01-01</td><td>London-Server</td><td>London Bridge</td><td>50</td><td>0</td></tr><tr><td>2008-01-01</td><td>London-Server</td><td>London West Minster</td><td>80</td><td>0</td></tr><tr><td>2008-01-01</td><td>Portugal-Server</td><td>Lisbon</td><td>5</td><td style="font-weight: bold; color: pink;">1</td></tr></table>[/code]Edited: Code sample got jacked up...Tue, 27 Nov 2012 13:38:18 GMTAlan.Bhttp://www.sqlservercentral.com/Forums/Topic1385334-1292-1.aspxThanks, what if I need to change font-weight at <tr> level?How do I access <tr>?Tue, 27 Nov 2012 12:07:30 GMTrightontargethttp://www.sqlservercentral.com/Forums/Topic1385334-1292-1.aspx[quote][b]eugene.pipko (11/19/2012)[/b][hr]Thank you for reply,So, for every <td>, if I need to change an attribute, I'd need to select 'td/@style' = ......?Thanks,[/quote]Yes. P.S. W3Schools is the best place to go for an HTML reference.Mon, 26 Nov 2012 16:15:03 GMTAlan.Bhttp://www.sqlservercentral.com/Forums/Topic1385334-1292-1.aspxThank you for reply,So, for every <td>, if I need to change an attribute, I'd need to select 'td/@style' = ......?Thanks,Mon, 19 Nov 2012 09:14:03 GMTrightontargethttp://www.sqlservercentral.com/Forums/Topic1385334-1292-1.aspxThis seems to be more of an HTML/CSS formatting question than a SQL Server question, so perhaps try this:1. Take the HTML created by the code you posted and output it with: select @tableHTML 2. Paste that output into Notepad and view it in a web browser.3. Manually edit it by trial and error it until you get the desired result using references such as the "[url=http://www.w3schools.com/html/html_css.asp]HTML Style Example - Font, Color and Size[/url]" example at w3schools.4. Put the formatting want back into the T-SQL.For example, this code makes the font of the red cells larger and they are sorted/ORDERed BY the number of failed records. (Is sorting what you meant by controlling the position of tr?) [code="sql"]SELECT td = Date, '', td = WebServerName, '', td = Location, '', td = cnt_RecsTotal, '','td/@style' = CASE WHEN cnt_RecsFailed > 0 THEN 'background-color:#FF3366; font-size:20pt;' END,td = cnt_RecsFailedFROM #tmpDailyReportORDER BY cnt_RecsFailedfor xml path('tr'), TYPE ) AS NVARCHAR(MAX) ) + N'</table>' + N'' ;[/code]Sat, 17 Nov 2012 16:38:06 GMTGeorge Rypyschttp://www.sqlservercentral.com/Forums/Topic1385334-1292-1.aspxHi all,I am trying to generate an HTML-formated email from sql server. I need to be able to control the font-size/position of the table cell <td> or table row <tr>.Here is the link of the sample I am trying to follow: [url]http://grounding.co.za/blogs/romiko/archive/2008/09/21/generating-professional-email-notifications-with-sql.aspx[/url]Could someone help?[code="sql"]IF (OBJECT_ID('#tmpDailyReport') IS NOT NULL)DROP TABLE #tmpDailyReportCREATE TABLE #tmpDailyReport( Date varchar( 12 ) Null, WebServerName varchar( 150 ) NOT Null, Location varchar( 150 ) NOT Null, cnt_RecsTotal int Null, cnt_RecsFailed int Null)INSERT INTO #tmpDailyReport( Date, WebServerName, Location, cnt_RecsTotal,cnt_RecsFailed)select '2008-01-01','Amsterdam-Server','Amsterdam',1000,2unionselect '2008-01-01','London-Server','London Bridge',50,0unionselect '2008-01-01','London-Server','London West Minster',80,0unionselect '2008-01-01','Portugal-Server','Lisbon',5,1DECLARE @tableHTML NVARCHAR(MAX) ;SET @tableHTML = N'<H3>Daily Failure Report</H3>' + N'<div><H5>Execution Date: <font color="blue">' + CONVERT(NVARCHAR(30), GETDATE(), 113) + '</FONT></H5></div>' + N'<table border="1">' + N'<tr><th>InsertDate</th><th>WebServerName</th><th>Location</th><th>Total Records</th><th>Failed Records</th>' + CAST ( (SELECT td = Date, '', td = WebServerName, '', td = Location, '', td = cnt_RecsTotal, '','td/@bgcolor' = CASE WHEN cnt_RecsFailed > 0 THEN '#FF3366' END,td = cnt_RecsFailedFROM #tmpDailyReportfor xml path('tr'), TYPE ) AS NVARCHAR(MAX) ) + N'</table>' + N'' ;drop table #tmpDailyReport [/code]Thanks,Thu, 15 Nov 2012 13:18:12 GMTrightontarget