SQL서버로 메일 보내기 ext.MSSQL2000

출처 : <a href="http://swynk.com" target="_blank">http://swynk.com</a>

Writer : Paul Wong (<a href="mailto:pwong@wesync.com">pwong@wesync.com</a>)

Date : 12/4/00 12:39:33 PM

순수하게 SQL서버로 메일을 보내기 입니다.

저는 SQL2000으로 테스트 해 보았는데 아주 잘 돌아 가네요..

sp_oa~~~라는 방법으로 데이터를 처리하는데 흥미 있군요.

역시나 방법은 찾으면 있난 봅니다.

도움 되시길 바랍니다.

 

--실행하기
exec pSendMail '보내는 사람 멜 주소', '받을사람멜주소', '제목','내용'

--저장 프로시져 생성
create procedure pSendMail(@From varchar(50), @To varchar(50), @Subject varchar(255), @Body varchar(8000)) as
begin
declare @object int
declare @hr int
declare @src varchar(255)
declare @desc varchar(255)

exec @hr = sp_OACreate 'CDONTS.NewMail', @object OUT
if @hr <> 0
begin
exec sp_OAGetErrorInfo @object, @src OUT, @desc OUT
select hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
return @hr
end

exec @hr = sp_OASetProperty @object, 'From', @From
if @hr <> 0
begin
exec sp_OAGetErrorInfo @object, @src OUT, @desc OUT
select hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
return @hr
end

exec @hr = sp_OASetProperty @object, 'To', @To
if @hr <> 0
begin
exec sp_OAGetErrorInfo @object, @src OUT, @desc OUT
select hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
return @hr
end

exec @hr = sp_OASetProperty @object, 'Subject', @Subject
if @hr <> 0
begin
exec sp_OAGetErrorInfo @object, @src OUT, @desc OUT
select hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
return @hr
end

exec @hr = sp_OASetProperty @object, 'Body', @Body
if @hr <> 0
begin
exec sp_OAGetErrorInfo @object, @src OUT, @desc OUT
select hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
return @hr
end

exec @hr = sp_OASetProperty @object, 'BodyFormat', 1
if @hr <> 0
begin
exec sp_OAGetErrorInfo @object, @src OUT, @desc OUT
select hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
return @hr
end

exec @hr = sp_OASetProperty @object, 'MailFormat', 1
if @hr <> 0
begin
exec sp_OAGetErrorInfo @object, @src OUT, @desc OUT
select hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
return @hr
end

exec @hr = sp_OAMethod @object, 'Send'
if @hr <> 0
begin
exec sp_OAGetErrorInfo @object, @src OUT, @desc OUT
select hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
return @hr
end

exec @hr = sp_OADestroy @object
if @hr <> 0
begin
exec sp_OAGetErrorInfo @object, @src OUT, @desc OUT
select hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
return @hr
end

print 'Message Sent.'
return 0
end

[출처] SQL서버로 메일 보내기 ext.MSSQL2000|작성자 진카자마