public knowledge blog_load(sender Virendra)

                                                                                                                          Nothing shocks me, I am a Software Engineer.

Send mail From SQL server

Posted by Virendra Dugar on January 22, 2009

It is a very common situation when it is required to send mails. There are so many ways to send mail. 

One way is to send mail through Sql server 2005.

There are two methods available
1. DataBase Mail
2. Thorugh OLE

Here is the Stored Procedure to send mail through OLE.

Just copy this prodedure and run it in your database…

–WSA_sp_SendEmail ‘virendra@mailserver.local’,’virendra@mailserver.local’,’Hi’,’Test Mail From CDO’
CREATE PROCEDURE [dbo].[WSA_sp_SendEmail]
@From varchar(100),
@To varchar(1000),
@Subject varchar(250),
@Body varchar(max),
@CC varchar(1000) = null
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
Print @From
EXEC @hr = sp_OASetProperty @iMsg, ‘Configuration.fields(“”).Value’,’2′
EXEC @hr = sp_OASetProperty @iMsg, ‘Configuration.fields(“”).Value’,
EXEC @hr = sp_OAMethod @iMsg, ‘Configuration.Fields.Update’, null
–Print @To
EXEC @hr = sp_OASetProperty @iMsg, ‘To’, @To
EXEC @hr = sp_OASetProperty @iMsg, ‘From’, @From
if (@Cc <> ”)
EXEC @hr = sp_OASetProperty @iMsg, ‘Cc’, @Cc
EXEC @hr = sp_OASetProperty @iMsg, ‘Subject’, @Subject
EXEC @hr = sp_OASetProperty @iMsg, ‘HtmlBody’, @Body
EXEC @hr = sp_OAMethod @iMsg, ‘Send’, NULL
IF @hr <>0
select @hr
–print @hr
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
Print @hr
IF @hr = 0
SELECT @output = ‘ Source: ‘ + @source
PRINT ‘hi’
PRINT @output
SELECT @output = ‘ Description: ‘ + @description
PRINT @output
PRINT ‘ sp_OAGetErrorInfo failed.’
EXEC @hr = sp_OADestroy @iMsg

*Please change the SMTP server address in the Procedure. Currently it is set to, Change this address according to your SMTP server Address.

To send the mail one have to “Enable OLE Automation”.

Go to Sql server source configuration area and enable OLE Automation from the options list.

Sql server Source Configuration area is under Start->Microsoft Sql Server-> Configuaration Tools..



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: