/* This
improved version of sp_SQLNotify. Added features:
1.
Send HTML text
2.
Send file attachments
NOTE: Need
to download and install Microsoft Access Database Engine 2010 Redistributable
NOTE: Need
to install SpliMe SQL function
NOTE:
sp_SQLNotify is originally from
http://www.howtogeek.com/howto/database/sending-automated-job-email-notifications-in-sql-server-with-smtp/
Example:
EXEC
sp_SQLNotify
'from@email.com',
'to@email.com',
'Your daily missed sales data report',
'<!DOCTYPE html><html><body><h1>My First
Heading</h1><p>My first
paragraph.</p></body></html>',
'C:\Temp\Test.txt;'
*/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[sp_SQLNotify]
@From varchar(500),
@To varchar(500),
@Subject varchar(1000)= "",
@Body varchar(4000) = "",
@Attachments varchar(2000) = "",
@Cc varchar(500) = NULL,
@BCc varchar(500) = NULL
/*********************************************************************
This
stored procedure takes the above parameters and sends an e-mail.
All of the
mail configurations are hard-coded in the stored procedure.
Comments
are added to the stored procedure where necessary.
Reference
to the CDOSYS objects are at the following MSDN Web site:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_messaging.asp
***********************************************************************/
AS
Declare @iMsg int
Declare @hr int
Declare @source varchar(500)
Declare @description varchar(500)
Declare @output varchar(4000)
Declare @property int
--*************
Create the CDO.Message Object ************************
EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT
--***************Configuring
the Message Object ******************
-- This is
to configure a remote SMTP server.
--
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_schema_configuration_sendusing.asp
EXEC @hr = sp_OASetProperty@iMsg,'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
-- This is
to configure the Server Name or IP address.
-- Replace
MailServerName by the name or IP of your SMTP Server.
EXEC @hr = sp_OASetProperty@iMsg,'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value','10.1.1.56'
-- Save
the configurations to the message object.
EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null
-- Set the
e-mail parameters.
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, 'Cc', @Cc
EXEC @hr = sp_OASetProperty @iMsg, 'Bcc', @BCc
-- If you
are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
--
EXEC @hr = sp_OASetProperty @iMsg, 'HTMLBody', @Body
EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body
-- Add all
attachements to the email
IF @Attachments <> ''
BEGIN
DECLARE @FilePath VARCHAR(200)
DECLARE AttachmentCollection CURSOR FOR SELECT LTRIM(RTRIM(Line))FROMSplitMe(@Attachments, ';')
OPEN AttachmentCollection
FETCH NEXT FROM AttachmentCollection INTO @FilePath
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC @hr = sp_OAMethod @iMsg, 'AddAttachment', @property OUT, @FilePath
FETCH NEXT FROM AttachmentCollection INTO @FilePath
END
CLOSE AttachmentCollection
DEALLOCATE AttachmentCollection
END
-- Send
the email
EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL
-- Sample
error handling.
IF @hr <>0
select @hr
BEGIN
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
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END
-- Do some
error handling after each step if you need to.
-- Clean
up the objects created.
EXEC @hr = sp_OADestroy @iMsg
PRINT 'Mail Sent!'
