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
|
Did you find the page informational and useful? Share it using one of your favorite social sites.
Recommended Books & Training Resources
