April 13, 2017 at 11:44 pm
Declare @tableHTML NVARCHAR(MAX);
Set @tableHTML =
N'<H3>NWH DSR</H3>' +
N'<table border="1">' +
N'<tr><th>Col1</th><th>Col2</th>' +
N'<th>Col3</th><th>Col4</th>' +
N'<th>Col5</th><th>Col6</th><th>Col7</th><th>Col8</th><th>Datetime</th></tr>' +
CAST ( ( Select td = Col1,'',
td = Col2,'',
td = Col3,'',
td = Col4,'',
td = Col5,'',
td = Col6,'',
td = Col7,'',
td = Col8,'',
td = Datetime,''
FROM Table
order by Col1,Col3
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX)) +
N'</table>';
EXEC msdb.dbo.sp_send_dbmail
@profile_Col3 = 'DEF@xyz.org',
@recipients = 'ABCD@xyz.org',
@subject = 'DSR',
@execute_query_database = 'msdb',
@body = @tableHTML,
@body_format = 'HTML'
The table values are displayed wrong as this: Correct value 135.9GB is displayed as 1.359000000000000e+002. under Col2 and every column where there is a number.
Datetime column displays values like this : 2017-04-13T22:00:12.877 and I need it in yyyy-mm-dd hh:mm:ss format.
How can I color code the header columns ?
How can I color code Col 8 based on below condition.
If Col 8 has values less than or equal to 10 it should be red.If it is between 11 and 30 it should be orange.
Thanks
How can I fix this?
April 14, 2017 at 8:39 am
sqlnewbie17 - Thursday, April 13, 2017 11:44 PMDeclare @tableHTML NVARCHAR(MAX);
Set @tableHTML =
N'<H3>NWH DSR</H3>' +
N'<table border="1">' +
N'<tr><th>Col1</th><th>Col2</th>' +
N'<th>Col3</th><th>Col4</th>' +
N'<th>Col5</th><th>Col6</th><th>Col7</th><th>Col8</th><th>Datetime</th></tr>' +
CAST ( ( Select td = Col1,'',
td = Col2,'',
td = Col3,'',
td = Col4,'',
td = Col5,'',
td = Col6,'',
td = Col7,'',
td = Col8,'',
td = Datetime,''
FROM Table
order by Col1,Col3
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX)) +
N'</table>';EXEC msdb.dbo.sp_send_dbmail
@profile_Col3 = 'DEF@xyz.org',
@recipients = 'ABCD@xyz.org',
@subject = 'DSR',
@execute_query_database = 'msdb',
@body = @tableHTML,
@body_format = 'HTML'The table values are displayed wrong as this: Correct value 135.9GB is displayed as 1.359000000000000e+002. under Col2 and every column where there is a number.
Datetime column displays values like this : 2017-04-13T22:00:12.877 and I need it in yyyy-mm-dd hh:mm:ss format.
How can I color code the header columns ?
How can I color code Col 8 based on below condition.
If Col 8 has values less than or equal to 10 it should be red.If it is between 11 and 30 it should be orange.
Thanks
How can I fix this?
How do you expect anybody to be able to help here? You haven't provided any information. Take a look at the first link in my signature for best practices when posting questions.
_______________________________________________________________
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/
April 14, 2017 at 11:03 am
As Sean already posted, there is no where near enough information for anyone to help. You may want to make sure you have the correct subject and are posting in the correct forum.
I could be wrong but what you have posted doesn't look like Powershell but rather like you are trying to send an HTML email and have the incorrect data types, precision for your query.
And that you have all different display requests for the HTML. If that's what you are trying, the query needs to return the results in the format as you want in the email. You need to get the query right before trying to send it as an HTML email.
Sue
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply