Send the result of two queries embedded in the body
of the message
Show Code
declare @sql varchar(2000)
set @sql = '
SELECT CountryRegionName,
convert(varchar(20),SUM(SalesYTD),1) as TotalSales
FROM sales.vsalesperson
GROUP BY CountryRegionName;
SELECT top 5 StateProvinceName,
convert(varchar(20),SUM(SalesYTD),1) as TotalSales
FROM sales.vsalesperson
GROUP BY StateProvinceName;'
exec master..xp_SMTPSendMail80
@from = 'Sales<sales@thorpesoftware.com>',
@recipient = 'SalesManager<support@thorpesoftware.com>',
@address = 'YourSMTPServer',
@subject = 'YTD Sales report',
@body = '<p>Sales reports by Country</p><br><br> <<query>><p>Sales reports by State<br><br> <<query>> </p><br><br>This is an automated report so please do not reply to this email address',
@qdatabase='adventureworks2008',
@html=1,
@qtableattribute='border=0 BGCOLOR="white" CELLSPACING=2 CELLPADDING="0" align=center',
@qtdattribute='NOWRAP',
@stylesheet = @ss,
@query = @sql
Send a simple informational message
Show Code
exec master..xp_SMTPSendMail80
'support@thorpesoftware.com',
'sqlserver@thorpesoftware.com',
'smtp server',
'Overnight Jobs failed'
or
exec master..xp_SMTPSendMail80 @recipient = 'support@thorpesoftware.com',
@from = 'sqlserver@thorpesoftware.com',
@address = 'smtp server',
@subject = 'Overnight Jobs failed'
Send an informational mail with
body text to multiple recipients with the SQL Server Error
log as an attachment
Show Code
exec master..xp_SMTPSendMail80
@recipient = 'support@thorpesoftware.com,dbas@thorpesoftware.com',
@from = 'sqlserver@thorpesoftware.com',
@address = 'smtp server',
@subject = 'Overnight Jobs successful',
@copy_recipients = 'sales@thorpesoftware.com',
@body = 'Attached please find the SQL Server Errorlog',
@attachments = 'C:\Program Files\Microsoft SQL Server\MSSQL\LOG\ERRORLOG'
Send the
result of a query formatted with a style
sheet
Show Code
declare @ss varchar(2000)
set @ss = 'P { font-size: 14pt;
font-family: arial,helvetica;
background-color : white;
text-align : center;}
table { width: 50%}
th { font-size: 10pt;
font-family : arial,helvetica;
font-style : italic;
color: #329CFF;
background-color: #FFFC6B}
td { font-size: 10pt;
font-family : arial,helvetica;
color : black;
background-color : #EFEFEF;
width : 50%;}'
declare @sql varchar(2000)
set @sql = ' SELECT *
FROM dbo.backuphistory'
exec master..xp_SMTPSendMail80 @from = 'SQLServer<SQLServer@thorpesoftware.com>',
@recipient = 'DBAs<support@thorpesoftware.com>',
@address = 'YourSMTPServer',
@subject = 'Backup Report',
@body = '<p>The following backups have completed</p>',
@qdatabase='master',
@html=1,
@qtableattribute='border=1 CELLSPACING=2 CELLPADDING="5" align=center',
@qtdattribute='NOWRAP',
@qtablecaption = '',
@stylesheet = @ss,
@query = @sql
