With XP_SendMail:
CREATE PROCEDURE dbo.foo AS
BEGIN
SET NOCOUNT ON
-- do some other actions
DECLARE @body VARCHAR(1024)
SET @body = 'foo was fired '+
CONVERT(VARCHAR, GETDATE())
EXEC master..xp_sendmail
@recipients='you@you.com',
@message = @body,
@subject = 'foo was fired.'
END
-- or you can do it conditionally:
CREATE PROCEDURE dbo.foo AS
BEGIN
SET NOCOUNT ON
-- do some other action
IF @@ROWCOUNT > 0
BEGIN
DECLARE @body VARCHAR(1024)
SET @body = 'foo was fired ' +
CONVERT(VARCHAR, GETDATE()) +
CHAR(13) + CHAR(10) +
CONVERT(VARCHAR, @@ROWCOUNT)
EXEC master..xp_sendmail
@recipients='you@you.com',
@message = @body,
@subject = 'foo was fired.'
END
END
With XP_SMTP_SendMail (which assumes you are running SQL Server 7.0 or higher, have installed XP_SMTP_SendMail, and have a valid and properly configured SMTP Server):
CREATE PROCEDURE dbo.foo AS
BEGIN
SET NOCOUNT ON
-- do some other actions
DECLARE @body VARCHAR(1024)
SET @body = 'foo was fired '+
CONVERT(VARCHAR, GETDATE())
EXEC master..xp_smtp_sendmail
@TO = 'you@you.com',
@from = 'someone@somewhere.com',
@message = @body,
@subject = 'foo was fired.',
@server = 'smtp.yourdomain.com'
END
-- or you can do it conditionally:
CREATE PROCEDURE dbo.foo AS
BEGIN
SET NOCOUNT ON
-- do some other action
IF @@ROWCOUNT > 0
BEGIN
DECLARE @body VARCHAR(1024)
SET @body = 'foo was fired ' +
CONVERT(VARCHAR, GETDATE()) +
CHAR(13) + CHAR(10) +
CONVERT(VARCHAR, @@ROWCOUNT)
EXEC master..xp_smtp_sendmail
@TO = 'you@you.com',
@from = 'someone@somewhere.com',
@message = @body,
@subject = 'foo was fired.',
@server = 'smtp.yourdomain.com'
END
END
If you need to send an attachment, you can simply add the following parameter:
@attachments = 'c:\attachment.txt'
--------------------------------------------------------------------------------
CDO.Message
This sample assumes you are running Windows 2000 or higher, have a user that has access to sp_OA* procedures, and have a valid and properly configured SMTP server:
CREATE PROCEDURE dbo.sendMail_With_CDOMessage
@to VARCHAR(64),
@subject VARCHAR(255),
@body VARCHAR(1024)
AS
BEGIN
SET NOCOUNT ON
DECLARE
@handle INT,
@return INT,
@s VARCHAR(64),
@sc VARCHAR(1024),
@up CHAR(27),
@from VARCHAR(64),
@server VARCHAR(255),
@filename VARCHAR(255)
SET @s = '"http://schemas.microsoft.com/cdo/configuration/'
SELECT
@s = 'Configuration.Fields(' + @s,
@up = 'Configuration.Fields.Update',
@from = 'someone@somewhere.com',
@server = 'smtp.yourdomain.com'
-- or IP address, e.g. '127.0.0.1'
-- if you want an attachment:
,@filename = 'C:\folder\file.ext'
EXEC @return = sp_OACreate 'CDO.Message', @handle OUT
SET @sc = @s + 'sendusing").Value'
EXEC @return = sp_OASetProperty @handle, @sc, '2'
SET @sc = @s + 'smtpserver").Value'
EXEC @return = sp_OASetProperty @handle, @sc, @server
EXEC @return = sp_OAMethod @handle, @up, NULL
EXEC @return = sp_OASetProperty @handle, 'To', @to
EXEC @return = sp_OASetProperty @handle, 'From', @from
EXEC @return = sp_OASetProperty @handle, 'Subject', @subject
EXEC @return = sp_OASetProperty @handle, 'TextBody', @body
IF @filename IS NOT NULL
EXEC @return = sp_OAMethod @handle, 'AddAttachment', NULL, @filename
EXEC @return = sp_OAMethod @handle, 'Send', NULL
IF @return <> 0
BEGIN
PRINT 'Mail failed.'
IF @from IS NULL
PRINT 'From address undefined.'
ELSE
PRINT 'Check that server is valid.'
ELSE
PRINT 'Mail sent.'
EXEC @return = sp_OADestroy @handle
END
GO
