Send Data Via Email in T-SQL
I am writing a stored procedure which collects all changes that were happend in a table for a given date and would like to send recordset to an administrator via email from SQL Batch Job.
Now untill now, I couldn't figure out is how to send recordset in an email body. Is it possible to construct email body based on the recordset obtained from some tsql logic.
Any help is apprici开发者_运维知识库ated
Thanks
TheITGuy
Look into the sp_send_dbmail procedure, which allows you to execute a query and send the results in the e-mail body or as a file attachment.
This might provide you some impetus:
CREATE TABLE #Temp
(
[Rank] [int],
[Player Name] [varchar](128),
[Ranking Points] [int],
[Country] [varchar](128)
)
INSERT INTO #Temp
SELECT 1,'Rafael Nadal',12390,'Spain'
UNION ALL
SELECT 2,'Roger Federer',7965,'Switzerland'
UNION ALL
SELECT 3,'Novak Djokovic',7880,'Serbia'
DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
SET @xml = CAST(( SELECT [Rank] AS 'td','',[Player Name] AS 'td','',
[Ranking Points] AS 'td','', Country AS 'td'
FROM #Temp ORDER BY Rank
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
SET @body ='<html><body><H3>Tennis Rankings Info</H3>
<table border = 1>
<tr>
<th> Rank </th> <th> Player Name </th> <th> Ranking Points </th> <th> Country </th></tr>'
SET @body = @body + @xml +'</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQL ALERTING', -- replace with your SQL Database Mail Profile
@body = @body,
@body_format ='HTML',
@recipients = 'bruhaspathy@hotmail.com', -- replace with your email address
@subject = 'E-mail in Tabular Format' ;
DROP TABLE #Temp
The firm I work for requires the name/value pair to be on separate lines rather than in a table. While my solution could use some tuning, after much work, I came up with a solution with css formatting. I can't figure out why, however, the width of the NAME column isn't 100px, as I have requested.
The special characters are < alt >+0182, < alt >+0183 and < alt >+0185, respectively.
I will continue to strive for a more elegant solution without so many replace functions but the firm is more concerned with a 'now' solution than an elegant one. Feedback/improvements would be appreciated.
The email arrives (in Outlook) formatted as such, which is our firm's preference:
Tennis Rankings Info
Rank: 1
Player Name: Rafael Nadal
Points: 12390
Country: Spain
Rank: 2
Player Name: Roger Federer
Points: 7965
Country: Switzerland
Rank: 3
Player Name: Novak Djokovic
Points: 7880
Country: Serbia
Here is my solution:
CREATE TABLE #Temp
(
[Rank] [int],
[Player Name] [varchar](128),
[Ranking Points] [int],
[Country] [varchar](128)
)
INSERT INTO #Temp
SELECT 1,'Rafael Nadal',12390,'Spain'
UNION ALL
SELECT 2,'Roger Federer',7965,'Switzerland'
UNION ALL
SELECT 3,'Novak Djokovic',7880,'Serbia'
DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
SET @xml = CAST(( SELECT '¶Rank:·º' + convert(varchar, [Rank]) AS 'br','','¶Player Name:·º' +[Player Name] AS 'br','',
'¶Points:·º' + convert(varchar, [Ranking Points]) AS 'br','', '¶Country:·º' + Country AS 'br'
FROM #Temp ORDER BY Rank
FOR XML PATH('br'), ELEMENTS ) AS NVARCHAR(MAX))
SET @body ='<html><head><style type=''text/css''>body{font-family:tahoma;font-size:9pt;}.h{width:100px;}</style></head><body><H3>Tennis Rankings Info</H3>'
SET @body = @body + @xml +'</body></html>'
set @body = replace(@body, '</br>', '<br />')
set @body = replace(@body, '<br><br>', '<p>')
set @body = replace(@body, '<br>', '')
set @body = replace(@body, '<br /><br />', '</p>')
set @body = replace(@body, '¶', '<span class=''h''>')
set @body = replace(@body, '·', '</span>')
set @body = replace(@body, 'º', ' ')
--select @body
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DBMail', -- replace with your SQL Database Mail Profile
@body = @body,
@body_format ='HTML',
@recipients = 'xxx@xxx.com', -- replace with your email address
@subject = 'E-mail in Tabular Format' ;
DROP TABLE #Temp
加载中,请稍侯......
精彩评论