September 16, 2011 at 8:07 am
A query result is shown as a table in html format in the Outlook mail. I can modify the header size in the table; but i cant modify the font size of the values in the table. any idea how to do this? Attaching the result as a text file does not look eye catching.
DECLARE @xml NVARCHAR(MAX)DECLARE @body NVARCHAR(MAX)
SET @xml =CAST(( SELECT top 1 COLUMN1 AS 'td','',COLUMN2 AS 'td','',COLUMN3 AS 'td','', COLUMN4 AS 'td','',COLUMN5 AS 'td','',COLUMN6 AS 'td'
FROM TESTTABLE FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
set @body = '<html>
<body>
<h4>PCR</h4>
<table border="1">
<tr>
<th><font size=1>COLUMN1</th>
<th><font size=1>COLUMN2</th>
<th><font size=1>COLUMN3</th>
<th><font size=1>COLUMN4</th>
<th><font size=1>COLUMN5</th>
<th><font size=1>COLUMN6</th>
</tr>'
set @body = @body + @xml +'</table></body></html>'+'<font face="Verdana" size="2" color="#333333"><p><br />Thanks,<br />Robert</p><p>'
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'test@test,com',
@body = @BODY,
@body_format ='HTML',
@subject ='Message Subject',@profile_name = 'Testprofile'
September 16, 2011 at 8:22 am
piece of cake.
add some CSS to the top of your command to put the default style on the td tags.
my example here is using font size 11 points...change it to what looks right to your needs.
ideally, you'd remove the inline font tags and do the same for the th tags as well.
set @body = '<html><head>' +
'<style>' +
'th {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:11pt;} ' +
'td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:11pt;} ' +
'</style>' +
'</head>' +
'<body>'
Lowell
September 16, 2011 at 8:31 am
Hi Lowell,
thanks for the code. But need further help; could you please show where to add your part of code (after makng size/font changes) in my code? I have nil knowledge of HTML/CSS. I changed like given below, but my mail is not in table format( also its gibberish with all columns in a single row)
set @body = '<html><head>' +
'<style>' +
'td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:11pt;} ' +
'</style>' +
'</head>' +
'<body>'
set @body = @body + @xml +'</table></body></html>'+'<font face="Verdana" size="2" color="#333333"><p><br />Thanks,<br />Robert</p><p></font>'
September 16, 2011 at 8:45 am
well, this is syntactically correct, and a good basisi for the way I would do it; i changed your @body variable name, i hate having a local variable the smae name as a parameter variable
DECLARE @xml NVARCHAR(MAX)
DECLARE @HTMLBody NVARCHAR(MAX)
SET @xml =CAST(( SELECT top 1 COLUMN1 AS 'td','',COLUMN2 AS 'td','',COLUMN3 AS 'td','', COLUMN4 AS 'td','',COLUMN5 AS 'td','',COLUMN6 AS 'td'
FROM TESTTABLE FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
set @HTMLBody = '<html><head>'
+ '<style>'
+ 'th {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:11pt;} '
+ 'td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:11pt;} '
+ '</style>'
+ '</head>'
+ '<body>'
+ '<h4>PCR</h4>'
+ '<table border="1">'
+ '<tr>'
+ '<th>COLUMN1</th>'
+ '<th>COLUMN2</th>'
+ '<th>COLUMN3</th>'
+ '<th>COLUMN4</th>'
+ '<th>COLUMN5</th>'
+ '<th>COLUMN6</th>'
+ '</tr>'
set @HTMLBody = @HTMLBody + @xml +'</table></body></html>'+'<font face="Verdana" size="2" color="#333333"><p><br />Thanks,<br />Robert</p><p>'
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'test@test,com',
@body = @HTMLBody,
@body_format ='HTML',
@subject ='Message Subject',@profile_name = 'Testprofile'
Lowell
September 16, 2011 at 12:27 pm
did my example work for you?
Lowell
September 17, 2011 at 10:19 am
Lowell, Thanks for the reply.
Apologies. I couldnt check the code. Windows patches and some monitoring tool is being installed in the SQL box.
Will surely get back to you, once i get my hands on the Server.
September 17, 2011 at 10:33 pm
Lowell, It worked like a charm. Thanks a lot.
Still one question; When i attach the results as an .XLS file, it opens as,
COLUMN1 COLUMN2 COLUMN3 COLUMN4
--------- ---------------------------------------- ------------
2010-12-16 07:05:00.000 value value value
(1 rows affected)
I have to modify it using "Text to Columns" either using an Macro/Script and then mail it. Can we avoid this by making the mail itself send it as a formatted file (without any scripts/macros)?
September 18, 2011 at 6:20 am
text to columns? do you mean that is 'rows to columns", or also known as a pivot or unpivot query?
the pseudocode you've got so far is not really enough for me to build you a working example, I'd really need the real query, and maybe the DDL of the table you are extracting it from, as well as a guestimate/example on the expected results.
here'san example that is my guess at what you are after:
CREATE TABLE #Temp ([Code] VARCHAR(6), [Panel No] INT, [Bay No] INT, [Closing Code] CHAR(1),
[Test Product] VARCHAR(3), [Oil Quality] INT)
INSERT INTO #Temp ([Code], [Panel No], [Bay No], [Closing Code], [Test Product], [Oil Quality])
SELECT 'CB0397', 0, 0, 'H', 'N/A', 265
SELECT [Code],[AttributeCode],[AttributeValue]
FROM
(SELECT
[Code],
CAST([Panel No] AS VARCHAR) as [Panel No],
CAST([Test Product] AS VARCHAR) as [Test Product],
CAST([Bay No] AS VARCHAR) as [Bay No],
CAST([Closing Code] AS VARCHAR) as [Closing Code],
CAST([Oil Quality] AS VARCHAR) as [Oil Quality]
FROM #Temp) p
UNPIVOT
([AttributeValue] FOR [AttributeCode] IN
([Test Product],[Panel No], [Bay No],[Closing Code])
)AS unpvt
GO
Lowell
September 19, 2011 at 5:35 am
I need to attach the result of an SELECT query as an Excel file in the DB mail. Can that be done? When I give the File name in the DBmail, I given .XLS extension; But the results are in text/.csv format with all the values in a single column. Hence, Text to Columns is in Excel is used to split them into different columns.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply