디스크 여유공간 체크 및 메일보내기~

declare @MB_Free int

create table #FreeSpace(

 Drive char(1), 

 MB_Free int)

insert into #FreeSpace exec xp_fixeddrives

select @MB_Free = MB_Free from #FreeSpace where Drive = 'C'

-- Free Space on C drive Less than Threshold

if @MB_Free < 40300

  exec master.dbo.xp_sendmail 

     @recipients ='koon74@naver.com',

     @subject ='C Drive 여유공간 임계치 이하',

     @message = 'Free space on C Drive 

                 has dropped below 1 gig'

                 

                 

다음 Query는 SQL Server에서 메일을 발송하는 예제입니다. 이 쿼리는 IIS-SMTP 서비스가 실행되고 있어야 합니다. 아래 코드는 CDONTS 컴포넌의 객체를 생성하여 보내는 방법입니다.

DECLARE @SenderAddress varchar(100)

DECLARE @RecipientAddress varchar(100)

DECLARE @Subject varchar(200)

DECLARE @Body varchar(8000)

DECLARE @oMail int --Object reference

DECLARE @resultcode int

SET @SenderAddress = 'serverinfo@serverinfo.pe.kr'

SET @RecipientAddress= 'webmaster@serverinfo.pe.kr'

SELECT @Subject = '이벤트 발생날짜 ' + CAST(getdate() AS varchar(12))

SET  @Body = 'This is the body of my email'

EXEC @resultcode = sp_OACreate 'CDONTS.NewMail', @oMail OUT

IF @resultcode = 0

BEGIN

   EXEC @resultcode = sp_OASetProperty @oMail, 'BodyFormat', 0

   EXEC @resultcode = sp_OASetProperty @oMail, 'MailFormat', 0

   EXEC @resultcode = sp_OASetProperty @oMail, 'Importance', 1

   EXEC @resultcode = sp_OASetProperty @oMail, 'From',@SenderAddress

   EXEC @resultcode = sp_OASetProperty @oMail, 'To',@RecipientAddress

   EXEC @resultcode = sp_OASetProperty @oMail, 'Subject',@Subject

   EXEC @resultcode = sp_OASetProperty @oMail, 'Body', @Body

   EXEC @resultcode = sp_OAMethod @oMail, 'Send', NULL

   EXEC sp_OADestroy @oMail

END 

출처 : http://www.wssplex.net