MSSQL로 메일보내기(펌)자료

--************************************************************************
--** 1. DataBase Mail 사용을위한구성옵션변경 **
--*************************************************************************
use master
go
-----------------------------------------------------------------------------
-- 고급구성옵션을수정하기위해SP_CONFIGURE를고급구성옵션으로변경해준다.
-----------------------------------------------------------------------------
sp_configure
go -- 기본설정값이기본구성옵션이기때문에16개정도만출력된다.
sp_configure 'show advanced options' , 1
go -- (0:기본구성옵션, 1:고급구성옵션이표시)
reconfigure
go -- 새로구성한옵션값을적용함
sp_configure
go
-----------------------------------------------------------------------------
-- DataBase Mail을사용할수있도록설정구성
-----------------------------------------------------------------------------
sp_configure 'Database Mail XPs' , 1
-- 0은데이터베이스메일을사용할수없음을나타냅니다(기본값).
-- 1은데이터베이스메일을사용할수있음을나타냅니다.
-- 이옵션은서버를중지했다가다시시작하지않아도즉시적용됩니다.
reconfigure
go
sp_configure 'Database Mail XPs'
go
-- 구성도구-> SQL server 노출영역구성-> 기능에대한노출영역구성
-- -> Database Engine -> 데이터베이스메일-> 데이터베이스메일저장프로시저사용체크
-- SQL server 에이전트재실행
--*************************************************************************
--** 2. DBase Mail Setting **
--*************************************************************************
-- smtp 계정에대한정보를저장할새데이터베이스메일계정생성
EXECUTE msdb.dbo.sysmail_add_account_sp
@ac count_name = 'MailServiceAccount'
, @description = 'Test Mail'
, @email_address = 'Email Address'
, @display_name = '메일주소의Display명'
, @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 ;
--*************************************************************************
--** 3. DB Mail Execute **
--*************************************************************************
-- A. E-mail 메세지송부하기
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'MailServiceAccount',
@recipients = 'younseun@younseun.co.kr',
@subject = 'Automated Success Message',
@body = 'The stored procedure finished successfully.<br><table><tr><td>a</td><td>b</td></tr></table>',
@body_format = 'HTML'
-- @profile_name : exec msdb.dbo.sysmail_help_profile_sp 로profile을확인할수있다.
-- DataBase mail 의프로필Setting시에정의된다.
--EXECUTE msdb.dbo.sysmail_add_profile_sp
--@profile_name = 'MailServiceProfile', @description = 'Profile used for database mail' ;
-- @recipients : 메일을수신받을Email Address를기술한다.
-- @subject : 메일제목
-- @body : 메일내용
-- @body_format : 메일내용Format을지정한다.
-- B. 쿼리의결과값을E-mail 내용, 또는E-mail 첨부파일로추가하여메일송부하는방법
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'MailServiceAccount',
@recipients = 'younseun@younseun.co.kr',
@query = 'SELECT COUNT(*) FROM admin.dbo.T_debug' ,
@subject = 'T_debug Count Result',
@body = 'admin.dbo.T_debug Count Result(파일첨부)',
@attach_query_result_as_file = 1 ;
-- @query : 수행될Query
-- @attach_query_result_as_file : 수행된Query 결과값의첨부파일추가여부(0:내용, 1:첨부파일)
--C. Query로html구조를생성한후에HTML 포맷으로E-mail송부하기
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
N'<H1>Work Order Report</H1>' +
N'<table border="1">' +
N'<tr><th>Work Order ID</th><th>Product ID</th>' +
N'<th>Name</th><th>Order Qty</th><th>Due Date</th>' +
N'<th>Expected Revenue</th></tr>' +
CAST ( ( SELECT td = wo.WorkOrderID, '',
td = p.ProductID, '',
td = p.Name, '',
td = wo.OrderQty, '',
td = wo.DueDate, '',
td = (p.ListPrice - p.StandardCost) * wo.OrderQty
FROM AdventureWorks2008R2.Production.WorkOrder as wo
JOIN AdventureWorks2008R2.Production.Product AS p
ON wo.ProductID = p.ProductID
WHERE DueDate > '2006-04-30'
AND DATEDIFF(dd, '2006-04-30', DueDate) < 2
ORDER BY DueDate ASC,
(p.ListPrice - p.StandardCost) * wo.OrderQty DESC
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;
EXEC msdb.dbo.sp_send_dbmail @recipients='younseun@younseun.co.kr',
@subject = 'Work Order List',
@body = @tableHTML,
@body_format = 'HTML' ;
--*************************************************************************
--** 4. 지우기(실제로는사용하지않는다.) **
--*************************************************************************
execute msdb.dbo.sysmail_delete_principalprofile_sp
@principal_name = 'public'
, @profile_name = 'mapbak_profile_name';
go
execute msdb.dbo.sysmail_delete_profileaccount_sp
@profile_name = 'mapbak_profile_name'
, @account_name = 'mapbak_account_name';
go
execute msdb.dbo.sysmail_delete_profile_sp
@profile_name = 'mapbak_profile_name';
go
execute msdb.dbo.sysmail_delete_account_sp
@account_name = 'mapbak_account_name';
go
--*************************************************************************
--*************************************************************************
--* SSIS 에서 메일 발송하기(SQL 실행 태스크 사용)
--*************************************************************************
--*************************************************************************
1. SQL 실행 태스크를 선택한다.

2. Connection을 설정한다.

3. SQL 쿼리에 메일 발송 script를 작성한다.