#title 데이터베이스 메일 보내기
[[TableOfContents]]

==== 노출영역 구성 ====
{{{
use master
go
sp_configure 'show advanced options' , 1
go
reconfigure with override
go
sp_configure 'Database Mail XPs' , 1
--go
--sp_configure 'SQL Mail XPs',0
go
reconfigure 
go 
}}}

==== DB메일 세팅 ====
{{{
EXECUTE msdb . dbo . sysmail_add_account_sp
@ac count_name = 'MailServiceAccount'
, @description = 'Test Mail'
, @email_address = '메일주소'
, @display_name = 'Databaser.Net'
, @username = '메일서버AccountID'
, @password = '패스워드 '
, @mailserver_name = 'smtp서버 주소'

EXECUTE msdb . dbo . sysmail_add_profile_sp
@profile_name = 'MailServiceProfile'
, @description = 'Profile used for database mail' ;

EXECUTE msdb . dbo . sysmail_add_profileaccount_sp
@profile_name = 'MailServiceProfile'
, @account_name = 'MailServiceAccount'
, @sequence_number = 1

EXECUTE msdb . dbo . sysmail_add_principalprofile_sp
@profile_name = 'MailServiceProfile'
, @principal_name = 'public'
, @is_default = 1 ; 
}}}

==== 메일보내기 ====
{{{
declare @body1 varchar ( 100 )
set @body1 = 'Server :' + @@servername + ' My First Database Email '
EXEC msdb . dbo . sp_send_dbmail
@recipients = '메일주소'
, @subject = 'My Mail Test'
, @body = @body1
, @body_format = 'HTML' ; 
}}}

==== 데이터베이스 메일 설정 지우기 ====
{{{
EXECUTE msdb.dbo.sysmail_delete_profileaccount_sp
@profile_name = 'MailServiceProfile'
, @account_name = 'MailServiceAccount'

EXECUTE msdb.dbo.sysmail_delete_profile_sp
@profile_name = 'MailServiceProfile' ;

EXECUTE msdb.dbo.sysmail_delete_acco unt_sp
@account_name = 'MailServiceAccount' ;

--exec msdb..sysmail_help_principalprofile_sp
EXECUTE msdb.dbo.sysmail_delete_principalprofile_sp
@principal_name = 'guest',
@profile_name = 'MailServiceProfile' ;
}}}

==== Query를 HTML로 만들어서 보내기 ====
{{{
DECLARE @tableHTML  NVARCHAR(MAX) ;

SET @tableHTML =
	N'<H1>정기점검:SSIS</H1>' +
	N'<table border="1">' +
	N'<tr><th>서버번호</th>' +
	N'<th>정기점검작업명</th>' +
	N'<th>작업시간</th>' +
	N'<th>IP</th>' + 
	N'<th>작업명</th>' + 
	N'<th>작업스텝명</th>' +
	N'<th>서브시스템</th>' +
	N'<th>서버명</th>' +
	N'<th>패키지명</th>' +
	N'</tr>' +
    CAST ( (
		select 
			td = ServerNo, ''
		,	td = 정기점검작업명, ''
		,	td = 작업시간, ''
		,	td = IP, ''
		,	td = job_nm, ''
		,	td = step_name, ''
		,	td = subsystem, ''
		,	td = ServerNM, ''
		,	td = PackageFileName 
		from work.dbo.테스트
		order by 1
              FOR XML PATH('tr'), TYPE 
    ) AS NVARCHAR(MAX) ) +
    N'</table>' ;

EXEC msdb.dbo.sp_send_dbmail @recipients='aaa@databaser.net',
    @subject = '정기점검:SSIS',
    @body = @tableHTML,
    @body_format = 'HTML' ;
}}}
==== 참고자료 ====
 * [http://www.mssqltips.com/sqlservertip/2578/setup-database-mail-to-use-a-gmail-hotmail-yahoo-or-aol-account/ Setup Database Mail to use a Gmail, Hotmail, Yahoo or AOL account]
 * [http://www.sqlservercentral.com/articles/Security/68873/ Using a Certificate Signed Stored Procedure to Execute sp_send_dbmail]
 * [http://www.mssqltips.com/tip.asp?tip=1736 Database Mail and SQL Agent Mail setup by using a script]
 * [http://www.mssqltips.com/tip.asp?tip=1732 SQL Server Database Mail Cleanup Procedures]