데이터 베이스를 통해서 메일 보내는 방법을 확인해보겠습니다.
Database Mail XPs 옵션을 사용하여 서버에서 데이터 베이스 메일을 활성화 해야합니다.
쿼리문입니다.
use master
go
sp_configure 'show advanced option',1
go
reconfigure with override
go
sp_configure 'database mail xps',1
go
reconfigure
go
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'MailServiceAccount'
, @description = 'DataBaser.Net Service Mail'
, @email_address = 'admin@databaser.net'
, @display_name = 'Databaser.Net'
, @username='wxxxxxx@domain.com
, @password='패스워드'
, @mailserver_name = '211.xxx.xxx.xxx'
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 ;
GUI 에서 확인해보도록하겠습니다.
시작 -> 프로그램 -> Microsoft Sql Server 2005 -> 구성영역 -> SQL Server 노출 영역 구성
기능에 대한 노출 영력 구성 클릭후 다음 화면의 구성을 체크하시기 바랍니다.
데이터 베이스 메일 -> 데이터베이스 메일 저장 프로시저 사용
이제 적용하면 SQL 상에서 메일을 사용하기 위한 셋팅은 모두 끝났습니다.
Management Studio 에서 다음 항목으로....
관리 -> 데이터 베이스 메일 -> 데이터베이스 메일 구성
위 그림에서 보는것과 같이 양식에 맞게 입력하시되 SMTP 인증 부분에 대해서 좀 고민해보시고 입력하시 바랍니다.
만약 Windows 2003 일경우 SMTP 인증 부분이(셋팅후 SMTP 컨트롤하지 않았다면) 익명인증으로 되어 있을 것입니다.그럼 그림과 같이 익명인증으로하시면됩니다. (그림에는 포트번호가 8040 으로 되어 있는데, 기본 25번포트이니 잊지마시구요)
그럼 다시 쿼리문 입력해서 메일이 잘가는지 확인해보도록하겠습니다.
exec msdb.dbo.sp_send_dbmail
@profile_name = 'hanbiro_hanbiro',
@recipients = 'wXXXXX@domain.com',
@subject='SQL_Mail_Test',
@body='SQL 메일 테스트 중입니다. 제발좀 가라잉~',
@body_format ='TEXT';
이상없이 잘가네요..
msdb에 가면 mail에 관련된 테이블들이 쭉~ 생긴건 보실수 있습니다.
그중 sysmail_mailitems 를 Select 해보면 지금 보낸 메일의 로그를 확인 할 수 있습니다.
- MSDN 내용 -
- [ @profile_name= ] 'profile_name'
메시지를 보내는 프로필의 이름입니다.profile_name은 sysname 유형이며 기본값은 NULL입니다.profile_name은 기존 데이터베이스 메일 프로필의 이름이어야 합니다.profile_name을 지정하지 않으면 sp_send_dbmail은 현재 사용자의 기본 개인 프로필을 사용합니다.사용자에게 기본 개인 프로필이 없을 경우 sp_send_dbmail은 msdb 데이터베이스의 기본 공개 프로필을 사용합니다.사용자에게 기본 개인 프로필이 없고 데이터베이스의 기본 공개 프로필도 없을 경우에는 @profile_name을 지정해야 합니다.
- [ @recipients= ] 'recipients'
메시지를 받을 전자 메일 주소 목록으로, 각 주소는 세미콜론으로 구분되어 있습니다.수신자 목록은 varchar(max) 유형입니다.이 매개 변수는 옵션이지만 @recipients, @copy_recipients 또는 @blind_copy_recipients 중 하나를 지정해야 합니다. 그렇지 않으면 sp_send_dbmail에서 오류를 반환합니다.
- [ @copy_recipients= ] 'copy_recipients'
참조로 메시지를 받을 전자 메일 주소 목록으로, 각 주소는 세미콜론으로 구분되어 있습니다.참조 수신자의 목록은 varchar(max) 유형입니다.이 매개 변수는 옵션이지만 @recipients, @copy_recipients 또는 @blind_copy_recipients 중 하나를 지정해야 합니다. 그렇지 않으면 sp_send_dbmail에서 오류를 반환합니다.
- [ @blind_copy_recipients= ] 'blind_copy_recipients'
숨은 참조로 메시지를 받을 전자 메일 주소 목록으로, 각 주소는 세미콜론으로 구분되어 있습니다.숨은 참조 수신자의 목록은 varchar(max) 유형입니다.이 매개 변수는 옵션이지만 @recipients, @copy_recipients 또는 @blind_copy_recipients 중 하나를 지정해야 합니다. 그렇지 않으면 sp_send_dbmail에서 오류를 반환합니다.
- [ @from_address= ] 'from_address'
전자 메일 메시지의 '보낸 사람 주소' 값입니다.이것은 메일 프로필의 설정을 재정의하는 데 사용되는 선택적 매개 변수입니다.이 매개 변수의 형식은 varchar(MAX)입니다.SMTP 보안 설정에 따라 재정의 허용 여부가 결정됩니다.매개 변수를 지정하지 않으면 기본값은 NULL입니다.
- [ @reply_to= ] 'reply_to'
전자 메일 메시지의 '회신 주소' 값입니다.전자 메일 주소만 유효한 값으로 허용됩니다.이것은 메일 프로필의 설정을 재정의하는 데 사용되는 선택적 매개 변수입니다.이 매개 변수의 형식은 varchar(MAX)입니다.SMTP 보안 설정에 따라 재정의 허용 여부가 결정됩니다.매개 변수를 지정하지 않으면 기본값은 NULL입니다.
- [ @subject= ] 'subject'
전자 메일 메시지의 제목입니다.제목은 nvarchar(255) 유형입니다.제목을 지정하지 않으면 기본값은 'SQL Server Message'입니다.
- [ @body= ] 'body'
전자 메일 메시지의 본문입니다.메시지 본문은 nvarchar(max) 유형이며 기본값은 NULL입니다.
- [ @body_format= ] 'body_format'
메시지 본문의 형식입니다.매개 변수는 varchar(20) 유형이며 기본값은 NULL입니다.이 매개 변수를 지정할 경우 나가는 메시지의 헤더가 보내져 메시지 본문이 지정된 형식임을 나타냅니다.매개 변수에 포함할 수 있는 값은 다음과 같습니다.
TEXT
HTML
기본값은 TEXT입니다.
- [ @importance= ] 'importance'
메시지의 중요도입니다.매개 변수는 varchar(6) 유형입니다.매개 변수에 포함할 수 있는 값은 다음과 같습니다.
Low
Normal
High
기본값은 Normal입니다.
- [ @sensitivity= ] 'sensitivity'
메시지의 기밀성입니다.매개 변수는 varchar(12) 유형입니다.매개 변수에 포함할 수 있는 값은 다음과 같습니다.
Normal
Personal
Private
Confidential
기본값은 Normal입니다.
- [ @file_attachments= ] 'file_attachments'
전자 메일 메시지에 첨부되는 파일 이름 목록으로, 각 파일 이름은 세미콜론으로 구분되어 있습니다.목록의 파일은 절대 경로로 지정해야 합니다.첨부 파일 목록은 nvarchar(max) 유형입니다.기본적으로 데이터베이스 메일의 첨부 파일은 파일당 1MB로 제한됩니다.
- [ @query= ] 'query'
실행할 쿼리입니다.쿼리 결과를 파일로 첨부할 수도 있고 전자 메일 메시지의 본문에 포함할 수도 있습니다.쿼리는 nvarchar(max) 유형이며 유효한 Transact-SQL 문을 포함할 수 있습니다.별도의 세션에서 쿼리가 실행되므로 sp_send_dbmail을 호출하는 스크립트의 지역 변수를 쿼리에 사용할 수 없습니다.
- [ @execute_query_database= ] 'execute_query_database'
저장 프로시저가 쿼리를 실행하는 데이터베이스 컨텍스트입니다.매개 변수는 sysname 유형이며 기본값은 현재 데이터베이스입니다.이 매개 변수는 @query가 지정된 경우에만 적용됩니다.
- [ @attach_query_result_as_file= ] attach_query_result_as_file
쿼리의 결과 집합이 첨부 파일로 반환되는지 여부를 지정합니다.attach_query_result_as_file은 bit 유형이며 기본값은 0입니다.
값이 0이면 쿼리 결과가 전자 메일 메시지 본문의 @body 매개 변수 내용 뒤에 포함됩니다.값이 1이면 결과가 첨부 파일로 반환됩니다.이 매개 변수는 @query가 지정된 경우에만 적용됩니다.
- [ @query_attachment_filename= ] query_attachment_filename
쿼리 결과 집합 첨부 파일에 사용할 파일 이름을 지정합니다.query_attachment_filename은 nvarchar(255) 유형이며 기본값은 NULL입니다.attach_query_result가 0이면 이 매개 변수는 무시됩니다.attach_query_result가 1이고 이 매개 변수가 NULL이면 데이터베이스 메일에서 임의로 파일 이름을 만듭니다.
- [ @query_result_header= ] query_result_header
쿼리 결과에 열 머리글을 포함할 것인지 여부를 지정합니다.query_result_header 값은 bit 유형입니다.값이 1이면 쿼리 결과에 열 머리글이 포함됩니다.값이 0이면 쿼리 결과에 열 머리글이 포함되지 않습니다.이 매개 변수의 기본값은 1입니다.이 매개 변수는 @query가 지정된 경우에만 적용됩니다.
- [ @query_result_width = ] query_result_width
쿼리 결과에 서식을 지정할 때 사용하는 문자 줄 너비입니다.query_result_width는 int 형식이며 기본값은 256입니다.10과 32767 사이의 값을 지정해야 합니다.이 매개 변수는 @query가 지정된 경우에만 적용됩니다.
- [ @query_result_separator= ] 'query_result_separator'
쿼리 출력에서 열을 구분하는 데 사용되는 문자입니다.구분 기호는 char(1) 유형입니다.기본값은 ' '(공백)입니다.
- [ @exclude_query_output= ] exclude_query_output
쿼리 실행 출력을 전자 메일 메시지로 반환할지 여부를 지정합니다.exclude_query_output은 bit이며 기본값은 0입니다.이 매개 변수가 0인 경우 sp_send_dbmail 저장 프로시저를 실행하면 쿼리 실행의 결과로 콘솔에 메시지가 출력됩니다.이 매개 변수가 1인 경우에는 sp_send_dbmail 저장 프로시저를 실행해도 쿼리 실행 메시지가 콘솔에 인쇄되지 않습니다.
- [ @append_query_error= ] append_query_error
@query 인수에 지정된 쿼리에서 오류가 반환될 때 전자 메일을 보낼지 여부를 지정합니다.append_query_error는 bit이며 기본값은 0입니다.이 매개 변수가 1이면 데이터베이스 메일에서 전자 메일 메시지의 본문에 쿼리 오류 메시지를 포함하여 전자 메일 메시지를 보냅니다.이 매개 변수가 0이면 데이터베이스 메일에서 전자 메일 메시지를 보내지 않으며 sp_send_dbmail이 실패를 나타내는 반환 코드 1을 표시하고 종료됩니다.
- [ @query_no_truncate= ] query_no_truncate
큰 가변 길이 데이터 형식(varchar(max), nvarchar(max), varbinary(max), xml, text, ntext, image 및 사용자 정의 데이터 형식)의 잘림을 방지하는 옵션을 사용하여 쿼리를 실행할지 여부를 지정합니다.설정된 경우 쿼리 결과에 열 머리글이 포함되지 않습니다.query_no_truncate 값은 bit 유형입니다.값이 0이거나 지정되지 않은 경우에는 쿼리의 열이 256자로 잘립니다.값이 1이면 쿼리의 열이 잘리지 않습니다.이 매개 변수의 기본값은 0입니다.
참고많은 데이터와 함께 사용하면 @query_no_truncate 옵션은 추가 리소스를 사용하므로 서버 성능이 느려질 수 있습니다.
- [ @query_result_no_padding ] @query_result_no_padding
형식은 bit이고,기본값은 0입니다.1로 설정하면 쿼리 결과가 채워지지 않으므로 파일 크기가 줄어들 수 있습니다. @query_result_no_padding을 1로 설정하고 @query_result_width 매개 변수를 설정하면 @query_result_no_padding 매개 변수가 @query_result_width 매개 변수를 덮어씁니다.
이 경우 오류는 발생하지 않습니다.
@query_result_no_padding을 1로 설정하고 @query_no_truncate 매개 변수를 설정하면 오류가 발생합니다.
- [ @mailitem_id= ] mailitem_id [ OUTPUT ]
선택적 출력 매개 변수는 메시지의 mailitem_id를 반환합니다.mailitem_id의 유형은 int입니다.
반환 코드 0은 성공을 의미합니다.다른 값은 실패를 의미합니다.실패한 문의 오류 코드는 @@ERRROR 변수에 저장됩니다.
성공 시 "Mail queued" 메시지를 반환합니다.
사용하기 전에 데이터베이스 메일 구성 마법사 또는 sp_configure를 사용하여 데이터베이스 메일을 설정해야 합니다.
sysmail_stop_sp는 외부 프로그램이 사용하는 Service Broker 개체를 중지하여 데이터베이스 메일을 중지합니다.sysmail_stop_sp를 사용하여 데이터베이스 메일이 중지되어도 sp_send_dbmail은 여전히 메일을 받습니다.데이터베이스 메일을 시작하려면 sysmail_start_sp를 사용합니다.
@profile을 지정하지 않으면 sp_send_dbmail은 기본 프로필을 사용합니다.전자 메일 메시지를 보내는 사용자에게 기본 개인 프로필이 있을 경우 이 프로필이 사용됩니다.사용자에게 기본 개인 프로필이 없을 경우 sp_send_dbmail은 기본 공개 프로필을 사용합니다.사용자의 기본 개인 프로필과 기본 공개 프로필이 둘 다 없을 경우 sp_send_dbmail은 오류를 반환합니다.
sp_send_dbmail은 내용이 없는 전자 메일 메시지를 지원하지 않습니다.전자 메일 메시지를 보내려면 적어도 @body, @query, @file_attachments, @subject 중 하나 이상을 지정해야 합니다.그렇지 않으면 sp_send_dbmail은 오류를 반환합니다.
데이터베이스 메일에서는 현재 사용자의 Microsoft Windows 보안 컨텍스트를 사용하여 파일에 대한 액세스를 제어합니다.그러므로 SQL Server 인증 방식으로 인증된 사용자는 @file_attachments를 사용하여 파일을 첨부할 수 없습니다.Windows에서는 SQL Server를 사용하여 원격 컴퓨터에서 다른 원격 컴퓨터로 자격 증명을 제공할 수 없습니다.그러므로 SQL Server를 실행하는 컴퓨터가 아닌 컴퓨터에서 명령을 실행할 경우 데이터베이스 메일은 네트워크 공유 위치에서 파일을 첨부할 수 없습니다.
@query와 @file_attachments를 둘 다 지정한 경우 파일을 찾을 수 없으면 쿼리가 여전히 실행되지만 전자 메일은 전송되지 않습니다.
쿼리를 지정한 경우 결과 집합의 서식은 인라인 텍스트로 지정됩니다.결과 내 이진 데이터는 16진수 형식으로 전송됩니다.
매개 변수 @recipients, @copy_recipients 및 @blind_copy_recipients는 세미콜론으로 구분된 전자 메일 주소 목록입니다.적어도 이 매개 변수 중 하나 이상을 지정해야 합니다. 그렇지 않으면 sp_send_dbmail은 오류를 반환합니다.
트랜잭션 컨텍스트 없이 sp_send_dbmail을 실행하면 데이터베이스 메일이 암시적 트랜잭션을 시작하고 커밋합니다.기존 트랜잭션 내에서 sp_send_dbmail을 실행하는 경우 사용자가 데이터베이스 메일에서 변경 내용을 커밋 또는 롤백할지를 결정합니다.내부 트랜잭션은 시작되지 않습니다.
sp_send_dbmail을 실행할 수 있는 권한은 기본적으로 msdb 데이터베이스에서 DatabaseMailUser 데이터베이스 역할의 모든 멤버로 설정됩니다.그러나 메시지를 보내는 사용자에게 요청하기 위해 프로필을 사용할 수 있는 권한이 없을 경우 sp_send_dbmail은 오류를 반환하고 메시지를 보내지 않습니다.
1.전자 메일 메시지 보내기
다음 예에서는 전자 메일 주소 danw@Adventure-Works.com을 사용하여 Dan Wilson에게 전자 메일 메시지를 보냅니다.메시지의 제목은 Automated Success Message입니다.메시지의 본문에는 'The stored procedure finished successfully'라는 문장이 포함되어 있습니다.
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Adventure Works Administrator',
@recipients = 'danw@Adventure-Works.com',
@body = 'The stored procedure finished successfully.',
@subject = 'Automated Success Message' ;
2.쿼리 결과를 포함하여 전자 메일 메시지 보내기
다음 예에서는 전자 메일 주소 danw@Adventure-Works.com을 사용하여 Dan Wilson에게 전자 메일 메시지를 보냅니다.메시지의 제목은 Work Order Count이며 이 메시지는 DueDate가 2004년 4월 30일부터 2일 내인 작업 주문 번호를 보여 주는 쿼리를 실행합니다.데이터베이스 메일은 결과를 텍스트 파일로 첨부합니다.
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Adventure Works Administrator',
@recipients = 'danw@Adventure-Works.com',
@query = 'SELECT COUNT(*) FROM AdventureWorks2012.Production.WorkOrder
WHERE DueDate > ''2004-04-30''
AND DATEDIFF(dd, ''2004-04-30'', DueDate) < 2' ,
@subject = 'Work Order Count',
@attach_query_result_as_file = 1 ;
3.HTML 전자 메일 메시지 보내기
다음 예에서는 전자 메일 주소 danw@Adventure-Works.com을 사용하여 Dan Wilson에게 전자 메일 메시지를 보냅니다.메시지의 제목은 Work Order List이며 이 메시지에는 DueDate가 2004년 4월 30일부터 2일 내인 작업 주문을 보여 주는 HTML 문서가 포함되어 있습니다.데이터베이스 메일은 메시지를 HTML 형식으로 보냅니다.
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 AdventureWorks.Production.WorkOrder as wo
JOIN AdventureWorks.Production.Product AS p
ON wo.ProductID = p.ProductID
WHERE DueDate > '2004-04-30'
AND DATEDIFF(dd, '2004-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='danw@Adventure-Works.com',
@subject = 'Work Order List',
@body = @tableHTML,
@body_format = 'HTML' ;
Rss Feed