-- enable Database mail
sp_CONFIGURE 'show advanced', 1
GO
RECONFIGURE
GO
sp_CONFIGURE 'Database Mail XPs', 1
GO
RECONFIGURE
GO
--1.
exec msdb.dbo.sysmail_add_account_sp
@account_name = N'account',
@description = N'Mail account for account e-mail.',
@email_address = N'account@microsoft.com',--your e-mail address
@display_name = N'account',
@mailserver_name = N'smtphost.redmond.corp.microsoft.com',-- stmp server
@username = N'account',
@password = N'xxxxxx'
--2.
declare @profileid int = 0
exec msdb.dbo.sysmail_add_profile_sp
@profile_name = N'profile',
@description = N'Fucheng Zhu',
@profile_id =@profileid output;
--3.
exec msdb.dbo.sysmail_add_profileaccount_sp
@profile_id = @profileid,
@account_name = N'account',
@sequence_number = 1 ;
--4.
DECLARE @tableHTML nvarchar(max)
SET @tableHTML =
N'<H1>Filter Info Report</H1>' +
N'<table border="1">' +
N'<tr><th>Name</th><th>Total Count</th><th>filter Count</th></tr>' +
CAST ( ( select td=FileName, '',
td=TotalCount, '',
td=filterCount ,''
from test.dbo.FilterInfo
FOR XML PATH('tr') ,TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;
--5.
exec msdb.dbo.sp_send_dbmail
@profile_name = N'profile',
@recipients = N'recipient1@gmail.com;recipient2@qq.com',
@copy_recipients = N'account@microsoft.com',
@subject = N'Testing SQL Send e-mail',
@body = @tableHTML,
@body_format = 'HTML',
@importance = N'High'
--6.
exec msdb.dbo.sysmail_delete_profileaccount_sp
@profile_name = N'profile',
@account_name = N'account' ;
--7.
exec sysmail_delete_profileaccount_sp
@profile_name = N'profile'
--8.
exec sysmail_delete_account_sp @account_name=N'account'
--9.
exec msdb.dbo.sysmail_delete_log_sp null,null