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 last published in June 2002

Dig Deeper on SQL Server Stored Procedures

PRO+

Content

Find more PRO+ content and other member only offers, here.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close