Sending mail from SQL Server using any SMTP Server

This set of SQL Server stored procedures will send email to a comma delimited list of emails using any SMTP server.

This set of stored procedures will 1) send e-mail to a comma delimited list of e-mails (7000 character maximum), 2) optionally carbon copy a comma delimited list of e-mails (7000 character maximum), and 3) provide support for a 7000 character body and 255 character subject using any SMTP server. It requires an external COM object, ASPEMail avaiable from http://www.aspemail.com/download.html.

I keep this in the model database, so each new database I create contains these procedures and the abilty to send e-mail.

There are three stored procedures in this set: sp_hexadecimal, sp_displayoaerrorinfo, and sp_sendmail. Usage:

 EXEC sp_SendMail @SMTPServer, -- smtp server address @From, -- From e-mail address @FromName, -- From friendly name (uses e-mail address if null) @AddAddress, -- Comma delimited Address list @CCAddress, -- Comma delimited CC list @Subject, -- EMail subject @Body, -- EMail body @Err -- Output Contains '0' if successful, -- Actual error text if failure DROP PROCEDURE sp_hexadecimal GO CREATE PROCEDURE sp_hexadecimal @binvalue varbinary(255),@hexvalue varchar(255) OUTPUT AS BEGIN SET NOCOUNT ON DECLARE @charvalue varchar(255), @i int, @length int, @hexstring char(16) SET @charvalue = '0x' SET @i = 1 SET @length = DATALENGTH(@binvalue) SET @hexstring = '0123456789abcdef' WHILE (@i <= @length) BEGIN DECLARE @tempint int, @firstint int, @secondint int SET @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1)) SET @firstint = FLOOR(@tempint/16) SET @secondint = @tempint - (@firstint*16) SET @charvalue = @charvalue + SUBSTRING(@hexstring, @firstint+1, 1) + SUBSTRING(@hexstring, @secondint+1, 1) SET @i = @i + 1 END SET @hexvalue = @charvalue END GO DROP PROCEDURE sp_displayoaerrorinfo GO CREATE PROCEDURE sp_displayoaerrorinfo @object int,@hresult int, @Err varchar(7000) OUTPUT AS BEGIN DECLARE @hrhex char(10), @hr int, @source varchar(255), @description varchar(255) EXECUTE sp_hexadecimal @hresult, @hrhex OUT SET @Err = ' HRESULT: ' + @hrhex EXECUTE @hr = sp_OAGetErrorInfo @object, @source OUT, @description OUT IF @hr = 0 BEGIN SET @Err = @Err + CHAR(13) + ' Source: ' + @source SET @Err = @Err + CHAR(13) + ' Description: ' + @description END ELSE BEGIN SET @Err = 'Unable to get error information' RETURN END END GO DROP PROC sp_SendMail go CREATE PROC sp_SendMail @SMTPServer varchar(50), @From varchar(100), @AddAddress varchar(7000), @FromName varchar(100) = NULL, @CCAddress varchar(7000)= NULL, @Subject varchar(256) = NULL, @Body varchar(7000) = NULL, @Err varchar(7000) OUTPUT AS /**************************************************************************/ /* Name : sp_SendMail */ /* Purpose : This stored procedure will send e-mail to any smtp server */ /* Dependant */ /* Database : sp_hexadecimal */ /* : sp_displayoaerrorinfo */ /* External : ASPEmail Com Object http://www.aspemail.com/download.html */ /* */ /* Usage: : EXECUTE sp_SendMail 
 
  , */ /* 
  
   , */ /* 
   
    , */ /* 
    
     , */ /* 
     
      , */ /* 
      
       , */ /*

      
     
    
   
  
 

*/ /**************************************************************************/ BEGIN DECLARE @object int, @hr int, @ErrInfo varchar(7000), @tmpAddr varchar(100) SELECT @AddAddress = RTRIM(@AddAddress) SELECT @CCAddress = RTRIM(@CCAddress) SELECT @Subject = RTRIM(@Subject) SELECT @SMTPServer = RTRIM(@SMTPServer) SELECT @From = RTRIM(@From) SELECT @FromName = RTRIM(@FromName) IF LEN(@AddAddress) > 0 OR LEN(@SMTPServer) > 0 OR LEN(@From) > 0 BEGIN /* Only do this if AddAddress, SMTPServer or From > 0 */ EXECUTE @hr = sp_OACreate 'Persits.MailSender', @object OUT IF @hr <> 0 BEGIN EXECUTE sp_displayoaerrorinfo @object, @hr, @ErrInfo OUT SELECT @Err = @ErrInfo RETURN END EXECUTE @hr = sp_OASetProperty @object, 'host', @SMTPServer IF @hr <> 0 BEGIN EXECUTE sp_displayoaerrorinfo @object, @hr, @ErrInfo OUT SELECT @Err = @ErrInfo RETURN END EXECUTE @hr = sp_OASetProperty @object, 'From',@From IF @hr <> 0 BEGIN EXECUTE sp_displayoaerrorinfo @object, @hr, @ErrInfo OUT SELECT @Err = @ErrInfo RETURN END IF @FromName IS NULL BEGIN /* If FromName is NULL, set it to From Address */ EXECUTE @hr = sp_OASetProperty @object, 'FromName',@From IF @hr <> 0 BEGIN EXECUTE sp_displayoaerrorinfo @object, @hr, @ErrInfo OUT SELECT @Err = @ErrInfo RETURN END END /* If FromName is NULL, set it to From Address */ ELSE BEGIN EXECUTE @hr = sp_OASetProperty @object, 'FromName',@FromName IF @hr <> 0 BEGIN EXECUTE sp_displayoaerrorinfo @object, @hr, @ErrInfo OUT SELECT @Err = @ErrInfo RETURN END END -- Add TO Address WHILE LEN(@AddAddress) > 0 BEGIN SELECT @tmpAddr = SUBSTRING(@AddAddress,1,CHARINDEX(',',@AddAddress)) IF LEN(@tmpAddr) = 0 BEGIN SELECT @tmpAddr = @AddAddress SELECT @AddAddress = '' END ELSE BEGIN SELECT @AddAddress = REPLACE(@tmpAddr,@AddAddress,'') END EXECUTE @hr = sp_OAMethod @object,'AddAddress',NULL, @tmpAddr IF @hr <> 0 BEGIN EXECUTE sp_displayoaerrorinfo @object, @hr, @ErrInfo OUT SELECT @Err = @ErrInfo RETURN END END -- Add CC Address if any WHILE LEN(@AddAddress) > 0 BEGIN SELECT @tmpAddr = SUBSTRING(@CCAddress,1,CHARINDEX(',',@CCAddress)) IF LEN(@tmpAddr) = 0 BEGIN SELECT @tmpAddr = @CCAddress SELECT @CCAddress = '' END ELSE BEGIN SELECT @CCAddress = REPLACE(@tmpAddr,@CCAddress,'') END EXECUTE @hr = sp_OAMethod @object,'AddCC',NULL, @tmpAddr IF @hr <> 0 BEGIN EXECUTE sp_displayoaerrorinfo @object, @hr, @ErrInfo OUT SELECT @Err = @ErrInfo RETURN END END EXECUTE @hr = sp_OASetProperty @object, 'Subject',@Subject IF @hr <> 0 BEGIN EXECUTE sp_displayoaerrorinfo @object, @hr, @ErrInfo OUT SELECT @Err = @ErrInfo END EXECUTE @hr = sp_OASetProperty @object, 'Body',@Body IF @hr <> 0 BEGIN EXECUTE sp_displayoaerrorinfo @object, @hr, @ErrInfo OUT SELECT @Err = @ErrInfo RETURN END EXECUTE @hr = sp_OAMethod @object, 'Send' IF @hr <> 0 BEGIN EXECUTE sp_displayoaerrorinfo @object, @hr, @ErrInfo OUT SELECT @Err = @ErrInfo RETURN END -- Destroy the object. EXECUTE @hr = sp_OADestroy @object IF @hr <> 0 BEGIN EXECUTE sp_displayoaerrorinfo @object, @hr, @ErrInfo OUT SELECT @Err = @ErrInfo RETURN END END /* Only do this if AddAddress, SMTPServer or From > 0 */ ELSE BEGIN /* If AddAddress, SMTPServer or From = 0 */ SELECT @Err = 'To Address, SMTP Server and From Address are required.' END /* If AddAddress, SMTPServer or From = 0 */ IF @Err IS NULL BEGIN SELECT @Err = '0' END END GO

This was first published in June 2002

Dig deeper on SQL Server Stored Procedures

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close