email from stored procedure

i want to know how can i send emails from SQL stored procedure? is it possible?

Yes.

If you are using SQL 2000, you should investigate xp_smtpmail. Check these sources:

http://www.sqldev.net/xp/xpsmtp.htm
http://www.aspfaq.com/show.asp?id=2403

If you are using SQL 2005, refer to Books Online, Topic: 'Database Mail'

|||

Hi Jassim,

I useed this script for SQL 2000. Take care of the parameters inside the procedure, such as server name or user account.

Code Snippet

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[spSendMail]

@.From varchar(150) ,

@.To varchar(150) ,

@.Bcc varchar(500) = null,

@.Subject varchar(400)=" ",

@.Body ntext =" "

--WITH ENCRYPTION

AS

Declare @.object int

Declare @.hr int

EXEC @.hr = sp_OACreate 'CDO.Message', @.object OUT

EXEC @.hr = sp_OASetProperty @.object, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'

EXEC @.hr = sp_OASetProperty @.object, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', 'mailserver.domain.com'

--BodyFormat = cdoBodyFormatHTML

EXEC @.hr = sp_OASetProperty @.object, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate").Value','1'

EXEC @.hr = sp_OASetProperty @.object, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusername").Value','DOMAIN\user'

EXEC @.hr = sp_OASetProperty @.object, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendpassword").Value','Pa$$w0rd'

EXEC @.hr = sp_OAMethod @.object, 'Configuration.Fields.Update', null

EXEC @.hr = sp_OASetProperty @.object, 'To', @.To

EXEC @.hr = sp_OASetProperty @.object, 'Bcc', @.Bcc

EXEC @.hr = sp_OASetProperty @.object, 'From', @.From

EXEC @.hr = sp_OASetProperty @.object, 'Subject', @.Subject

--use TextBody for plain text

EXEC @.hr = sp_OASetProperty @.object, 'HTMLBody', @.Body

EXEC @.hr = sp_OAMethod @.object, 'Send', NULL

--?

IF @.hr <> 0

BEGIN

EXEC sp_OAGetErrorInfo @.object

RETURN @.object

END

PRINT 'success'

EXEC @.hr = sp_OADestroy @.object

GO

and also you can use Arnie's links or in case of SQL 2005, use sp_send_dbmail.

Regards,

Janos