데이터베이스 메일 보내기

1. 실행하기

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


2. 노출 영역구성

3. 실행

EXECUTE msdb.dbo.sysmail_add_account_sp

             @account_name = 'MailServiceAccount4'

,            @description = 'chiwonii Service Mail'

,            @email_address = 'chiwonii@kaoni.com'  -- 실제 이메일

,            @display_name = '권지원' -- 보낸사람이름

,            @username='chiwonii@kaoni.com'

,            @password='비밀번호'

,            @mailserver_name = 'asp.kaoni.com' -- 메일서버주소

EXECUTE msdb.dbo.sysmail_add_profile_sp

             @profile_name = 'MailServiceProfile4'

,            @description = 'Profile used for database mail';

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp

             @profile_name = 'MailServiceProfile4'

,            @account_name = 'MailServiceAccount4'

,            @sequence_number = 1

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp

             @profile_name = 'MailServiceProfile4'

,            @principal_name = 'public'

,            @is_default = 1 ;

/*

--참고: 지우기

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_account_sp

    @account_name = 'MailServiceAccount' ;

--exec msdb..sysmail_help_principalprofile_sp

EXECUTE msdb.dbo.sysmail_delete_principalprofile_sp

    @principal_name = 'guest',

    @profile_name = 'MailServiceProfile' ;

*/

--메일보내기

declare @body1 varchar(100)

set @body1 = 'Server :'+@@servername+ ' 감격의 데이터 베이스 메일이 전송됩니다. '

EXEC msdb.dbo.sp_send_dbmail

             @recipients='chiwonii@KAONI.COM'

,            @subject = '메일테스트 입니다.'

,            @body = @body1

,            @body_format = 'HTML' ;