Entry

How to send Email from MS SQL Database Stored Procedure

Sep 10th, 2004 10:22
Lavanya Simon,

CREATE Procedure sn_SMTPMail	
/*********************************************************************
** Lavanya Simon 
**
** PURPOSE:
**    This stored procedure is used to send email from MS SQL Database 
**     
** TABLE USED:
**    TEMP 
** RESULT:
**    Email Sent.
** Command:
**    exec sn_SMTPMail
**********************************************************************/
AS		
SET nocount on		
declare @oMail int --Object reference	
declare @resultcode int		
EXEC @resultcode = sp_OACreate 'CDONTS.NewMail', @oMail OUT		
if @resultcode = 0	
BEGIN		
	DECLARE @t int
	DECLARE cur CURSOR FOR Select id from temp where checkrecord 
= 'Y' and termdate > getdate() - 10
	OPEN cur
	FETCH NEXT FROM cur INTO @t
	WHILE @@FETCH_STATUS = 0 BEGIN
		EXEC @resultcode = sp_OASetProperty 
@oMail, 'From', 'lavanyasimon@yahoo.com'
		EXEC @resultcode = sp_OASetProperty 
@oMail, 'To', 'simonnadar@yahoo.com'
		EXEC @resultcode = sp_OASetProperty @oMail, 'Subject', 
@t
		EXEC @resultcode = sp_OASetProperty @oMail, 'Body', 'ID 
Expires in next 10 days. Please Take Appropriate Action'
		EXEC @resultcode = sp_OAMethod @oMail, 'Send', NULL	
		
		EXEC sp_OADestroy @oMail
		FETCH NEXT FROM cur INTO @t
		END
		CLOSE cur
		DEALLOCATE cur
END		
SET nocount off
GO
 ****** End of Code ******

Lavanya Simon - Simon Nadar