--출처 <a href="http://support.microsoft.com/default.aspx?scid=kb;ko;312839" target="_blank">http://support.microsoft.com/default.aspx?scid=kb;ko;312839</a>
--요기부터 실행--
create PROCEDURE [dbo].[sp_send_cdosysmail]
@From varchar(100) ,
@To varchar(100) ,
@Subject varchar(100)=" ",
@Body varchar(4000) =" "
/*********************************************************************
This stored procedure takes the above parameters and sends an e-mail.
All of the mail configurations are hard-coded in the stored procedure.
Comments are added to the stored procedure where necessary.
Reference to the CDOSYS objects are at the following MSDN Web site:
<a href="http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_messaging.asp" target="_blank">http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_messaging.asp</a>
***********************************************************************/
AS
Declare @iMsg int
Declare @hr int
Declare @source varchar(255)
Declare @description varchar(500)
Declare @output varchar(1000)
--************* Create the CDO.Message Object ************************
EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT
--***************Configuring the Message Object ******************
-- This is to configure a remote SMTP server.
-- <a href="http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_schema_configuration_sendusing.asp" target="_blank">http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_schema_configuration_sendusing.asp</a>
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("<a href="http://schemas.microsoft.com/cdo/configuration/sendusing" target="_blank">http://schemas.microsoft.com/cdo/configuration/sendusing</a>").Value','2'
-- This is to configure the Server Name or IP address.
-- Replace MailServerName by the name or IP of your SMTP Server.
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("<a href="http://schemas.microsoft.com/cdo/configuration/smtpserver" target="_blank">http://schemas.microsoft.com/cdo/configuration/smtpserver</a>").Value', '메일서버 아이피'
-- Save the configurations to the message object.
EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null
-- Set the e-mail parameters.
EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject
-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body
EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL
-- Sample error handling.
IF @hr <>0
select @hr
BEGIN
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END
-- Do some error handling after each step if you need to.
-- Clean up the objects created.
EXEC @hr = sp_OADestroy @iMsg
go
create PROCEDURE [dbo].[sp_send_mail]
as
declare @Body varchar(4000)
select @Body = '(SQL에러발생) 쥐포 백업 서버 백업 실패'
exec sp_send_cdosysmail 'SQL관리자<<a href="mailto:webmaster@realzipo.com">webmaster@realzipo.com</a>>"','<a href="mailto:webmaster@realzipo.com">webmaster@realzipo.com</a>','sql 백업 실패 메일',@Body
-- 요기 까지 실행
/*설명
위에 두개의 Procedure를 실행 한다. 찬찬히 읽어 보세요.. 뭐가 뭔지는 잘모르지만
이때 아래의 sp_send_mail 의 @Body 는 서버에 따라 바꿔서 어느 서버에서 메일이 왔는지 구분해 준다.(db서버가 여러개일때)
*/
-------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------
exec sp_send_mail --요건 패키지 또는 백업 실패시 프로시져를 추가해 주면 됩니다.
-------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------
[출처] SQL로 메일(Mail) 보내기|작성자 진카자마