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 appriciated

Thanks

TheITGuy

标签： sql sql-server sql-server-2005 tsql

3条回答

萌系小妹纸

2楼-- · 2019-07-25 12:36

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.

0人赞添加讨论(0) 举报

女痞

3楼-- · 2019-07-25 12:38

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, 'º', '&nbsp;&nbsp;')

--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

0人赞添加讨论(0) 举报

Anthone

4楼-- · 2019-07-25 12:53

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

0人赞添加讨论(0) 举报

Send Data Via Email in T-SQL

Tennis Rankings Info

采纳回答

编辑标签

举报内容

检举类型

检举原因

检举说明(必填)

打开微信“扫一扫”，打开网页后点击屏幕右上角分享按钮

付费偷看金额在0.1-10元之间