Did you find this useful?
Socialize it today.


Sending Email Using CDOSYS

Monday, January 09, 2012

tags SQL

Unfortunately for MS SQL server, SQL Mail may not always be the first choice in your toolbox when sending out messages from your stored procedures. Instead of using SQL Mail, you can create an easy stored procedure to handle your messaging needs. The only requirement is that you need access to a mail server running SMTP services. The SMTP service can be local on the machine or your can use a remote server. The example shown below is the most basic example. You can add additional paramters such as @CC and @BCC for carbon and blind copying recipients. In addition, you can add additional OASetProperties for specifying the SMTP port number if the default of 25 is not used as well as authentication options. Finally, you may want to add error handling options as well.

Syntax



USE [databaseName]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[SendMail]
@From varchar(255) ,
@To varchar(255) ,
@Subject varchar(255)=" ",
@Body varchar(MAX) =" "

AS
BEGIN

Declare @iMsg int
Declare @hr int
Declare @source varchar(255)
Declare @description varchar(500)
Declare @output varchar(1000)

EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', 'mailserver hostname or IP'

EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null
EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject
EXEC @hr = sp_OASetProperty @iMsg, 'HTMLBody', @Body
EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL

IF (@iMsg IS NOT NULL)
BEGIN
EXEC @hr=sp_OADestroy @iMsg
END
 
END

GO


Optional Code



/* Error handling....add to each section if you wish to capture an error per section */
/* Rather than 'printing', you could add an INSERT INTO statement */
/* and send the information to another table. */

IF @hr <>0
BEGIN
SELECT @hr
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
RETURN
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END


Other Properties for sp_OASetProperty



Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = "1"
Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusername") = "username"
Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "password"
Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60

Please help us spread the word by socializing it today!

email contact us

Did you find something wrong with the information on this page? Please take a moment to report it to us so that we can continue to improve the quality of the information on this site. Click here to report an issue with this page.



Recommended Books & Training Resources

SQL Cookbook Head First SQL: Your Brain on SQL A Learners Guide